SQL query: How to only display certain rows when using aliasing?

0
votes

I am trying to only display rows where rownum is between 0 and 24. This will be dynamic and the range will change depending on which page the user is on. For some reason the below query executes forever and never returns a result. If I delete this:

WHERE 
    rownum >= 0 AND rownum < 24 
ORDER BY 
    rownum;

The query then executes without issue.

How can I fix this query so that it will do what I want? and why is this one not working?:

WITH cs AS 
( 
    SELECT 
        ROW_NUMBER() OVER (ORDER BY [RCA].[I_UID]) AS rownum, 
        [RCA].[I_UID], 
        [RCA].[I_TYPE_UID], 
        [RCA].[I_I_C], 
        [RCA].[L_D], 
        [RCA].[SOME_DATE], 
        [RCA].[ANOTHER_DATE], 
        [RCA].[ANOTHER_DATED], 
        [RCA].[L_ED_DATE], 
        [RCA].[L_ED_USER_UID], 
        [RCA].[PNT_UID], 
        [RCA].[FA_UID], 
        [RCA].[D_LE], 
        [RCA].[DIGITAL_A], 
        [RCA].[LOK], 
        [RCA].[KILP], 
        [RCA].[LKP], 
        [RCA].[BLT], 
        [RCA].[LAT], 
        [RCA].[JUT], 
        [RCA].[S_UID], 
        [S_A].[FAT_UID], 
        [S_A].[RD_UID], 
        [S_A].[S_CODE], 
        [S_A].[S_NAME], 
        [S_A].[S_TYPE_UID], 
        [S_A].[S_LENGTH], 
        [S_A].[CUP_LIMIT], 
        [S_A].[S_N_C], 
        [S_A].[S_N_D_JCT_TYPE_UID], 
        [S_A].[S_N_D_NAME], 
        [S_A].[ENC], 
        [S_A].[ENJT_UID], 
        [S_A].[ENT_NAME], 
        [S_A].[N_UID], 
        [S_A].[R_UID], 
        [S_A].[A_UID], 
        [S_A].[D_UID], 
        [S_A].[DI_UID], 
        [S_A].[SU_UID], 
        [S_A].[BS_T], 
        [S_A].[CLU_UID], 
        [S_A].[MH_UID], 
        [S_A].[EN_UID], 
        [S_A].[OWN_UID], 
        [S_A].[AG_UID], 
        [S_A].[DPT], 
        [S_A].[NPI], 
        [S_A].[R_W_C], 
        [S_A].[S_L_UID], 
        [S_A].[S_LF_T_CODE], 
        [S_A].[FT_I_UID], 
        [S_A].[F_O_UID], 
        [S_A].[DRN_C], 
        [S_A].[SUY_UID], 
        [S_A].[S_L_CS], 
        [S_A].[SUY], 
        [S_A].[MAX], 
        [S_A].[STRA_NO], 
        [S_A].[NOP], 
        [S_A].[CWXSP], 
        [S_A].[OFFCWXSP], 
        [S_A].[UK_M_HR_UID], 
        [S_A].[FWAY_M_HR_UID], 
        [S_A].[CYCLE_M_HR_UID], 
        [S_A].[UK_R_ROU_UID], 
        [S_A].[UK_R_DOT_CLUIFICATION_UID], 
        [S_A].[L_UID], 
        [S_A].[LOCAL_HR], 
        [S_A].[PG_DATE], 
        [S_A].[LCA_DIS_UID], 
        [S_A].[CWT], 
        [S_A].[LCA_OCC_L_UID], 
        [S_A].[SKL], 
        [S_A].[SKG] 
    FROM 
        RCA 
    LEFT OUTER JOIN 
        S_A ON RCA.S_uid = S_A.S_uid
    WHERE 
        [RCA].[SOME_DATE] <= '02 december 2020'
        AND [RCA].[ANOTHER_DATE] >= '02 december 2020'
        AND [S_A].[SOME_DATE] <= '02 december 2020'
        AND [S_A].[ANOTHER_DATE] >= '02 december 2020'
) 
SELECT *
FROM cs
WHERE 
    rownum >= 0 AND rownum < 24 
ORDER BY 
    rownum;

UPDATE:

I have updated my query using OFFSET / FETCH for pagination. The query executes successfully only when I order by descending. When change it to ORDER BY rownum ASC, the query executes forever without returning any results. This is the query that works, however I require the results to be in ascending order:

SELECT 
        [RCA].[I_UID], 
        [RCA].[I_TYPE_UID], 
        [RCA].[I_I_C], 
        [RCA].[L_D], 
        [RCA].[SOME_DATE], 
        [RCA].[ANOTHER_DATE], 
        [RCA].[ANOTHER_DATED], 
        [RCA].[L_ED_DATE], 
        [RCA].[L_ED_USER_UID], 
        [RCA].[PNT_UID], 
        [RCA].[FA_UID], 
        [RCA].[D_LE], 
        [RCA].[DIGITAL_A], 
        [RCA].[LOK], 
        [RCA].[KILP], 
        [RCA].[LKP], 
        [RCA].[BLT], 
        [RCA].[LAT], 
        [RCA].[JUT], 
        [RCA].[S_UID], 
        [S_A].[FAT_UID], 
        [S_A].[RD_UID], 
        [S_A].[S_CODE], 
        [S_A].[S_NAME], 
        [S_A].[S_TYPE_UID], 
        [S_A].[S_LENGTH], 
        [S_A].[CUP_LIMIT], 
        [S_A].[S_N_C], 
        [S_A].[S_N_D_JCT_TYPE_UID], 
        [S_A].[S_N_D_NAME], 
        [S_A].[ENC], 
        [S_A].[ENJT_UID], 
        [S_A].[ENT_NAME], 
        [S_A].[N_UID], 
        [S_A].[R_UID], 
        [S_A].[A_UID], 
        [S_A].[D_UID], 
        [S_A].[DI_UID], 
        [S_A].[SU_UID], 
        [S_A].[BS_T], 
        [S_A].[CLU_UID], 
        [S_A].[MH_UID], 
        [S_A].[EN_UID], 
        [S_A].[OWN_UID], 
        [S_A].[AG_UID], 
        [S_A].[DPT], 
        [S_A].[NPI], 
        [S_A].[R_W_C], 
        [S_A].[S_L_UID], 
        [S_A].[S_LF_T_CODE], 
        [S_A].[FT_I_UID], 
        [S_A].[F_O_UID], 
        [S_A].[DRN_C], 
        [S_A].[SUY_UID], 
        [S_A].[S_L_CS], 
        [S_A].[SUY], 
        [S_A].[MAX], 
        [S_A].[STRA_NO], 
        [S_A].[NOP], 
        [S_A].[CWXSP], 
        [S_A].[OFFCWXSP], 
        [S_A].[UK_M_HR_UID], 
        [S_A].[FWAY_M_HR_UID], 
        [S_A].[CYCLE_M_HR_UID], 
        [S_A].[UK_R_ROU_UID], 
        [S_A].[UK_R_DOT_CLUIFICATION_UID], 
        [S_A].[L_UID], 
        [S_A].[LOCAL_HR], 
        [S_A].[PG_DATE], 
        [S_A].[LCA_DIS_UID], 
        [S_A].[CWT], 
        [S_A].[LCA_OCC_L_UID], 
        [S_A].[SKL], 
        [S_A].[SKG] 
    FROM 
        RCA 
    INNER JOIN 
        S_A ON RCA.S_uid = S_A.S_uid
    WHERE 
        [RCA].[SOME_DATE] <= '02 december 2020'
        AND [RCA].[ANOTHER_DATE] >= '02 december 2020'
        AND [S_A].[SOME_DATE] <= '02 december 2020'
        AND [S_A].[ANOTHER_DATE] >= '02 december 2020'

ORDER BY 
    [RCA].[I_UID] DESC

    OFFSET 0 ROWS
    FETCH FIRST 24 ROWS ONLY;

Final working solution:

;WITH cs AS 
( 
    SELECT 
        t.S_UID,
        t.I_UID,
        t.[I_TYPE_UID], 
        t.[I_I_C], 
        t.[L_D], 
        t.[SOME_DATE], 
        t.[ANOTHER_DATE], 
        t.[ANOTHER_DATED], 
        t.[L_ED_DATE], 
        t.[L_ED_USER_UID], 
        t.[PNT_UID], 
        t.[FA_UID], 
        t.[D_LE], 
        t.[DIGITAL_A], 
        t.[LOK], 
        t.[KILP], 
        t.[LKP], 
        t.[BLT], 
        t.[LAT], 
        t.[JUT], 
        S_A.[FAT_UID], 
        S_A.[RD_UID], 
        S_A.[S_CODE], 
        S_A.[S_NAME], 
        S_A.[S_TYPE_UID], 
        S_A.[S_LENGTH], 
        S_A.[CUP_LIMIT], 
        S_A.[S_N_C], 
        S_A.[S_N_D_JCT_TYPE_UID], 
        S_A.[S_N_D_NAME], 
        S_A.[ENC], 
        S_A.[ENJT_UID], 
        S_A.[ENT_NAME], 
        S_A.[N_UID], 
        S_A.[R_UID], 
        S_A.[A_UID], 
        S_A.[D_UID], 
        S_A.[DI_UID], 
        S_A.[SU_UID], 
        S_A.[BS_T], 
        S_A.[CLU_UID], 
        S_A.[MH_UID], 
        S_A.[EN_UID], 
        S_A.[OWN_UID], 
        S_A.[AG_UID], 
        S_A.[DPT], 
        S_A.[NPI], 
        S_A.[R_W_C], 
        S_A.[S_L_UID], 
        S_A.[S_LF_T_CODE], 
        S_A.[FT_I_UID], 
        S_A.[F_O_UID], 
        S_A.[DRN_C], 
        S_A.[SUY_UID], 
        S_A.[S_L_CS], 
        S_A.[SUY], 
        S_A.[MAX], 
        S_A.[STRA_NO], 
        S_A.[NOP], 
        S_A.[CWXSP], 
        S_A.[OFFCWXSP], 
        S_A.[UK_M_HR_UID], 
        S_A.[FWAY_M_HR_UID], 
        S_A.[CYCLE_M_HR_UID], 
        S_A.[UK_R_ROU_UID], 
        S_A.[UK_R_DOT_CLUIFICATION_UID], 
        S_A.[L_UID], 
        S_A.[LOCAL_HR], 
        S_A.[PG_DATE], 
        S_A.[LCA_DIS_UID], 
        S_A.[CWT], 
        S_A.[LCA_OCC_L_UID], 
        S_A.[SKL], 
        S_A.[SKG] 
       
    FROM 
        RCA AS t
    LEFT OUTER JOIN 
        S_A ON t.S_UID = S_A.S_UID
    
    WHERE 
        t.[SOME_DATE] <= '02 december 2020'
        AND t.[ANOTHER_DATE] >= '02 december 2020'

    ORDER BY t.[I_UID]
        OFFSET 24 * 0 ROWS
    FETCH NEXT 24 ROWS ONLY
) 

SELECT *
FROM cs
ORDER BY 
cs.I_UID
1
sqlssmssql-server-2014row-number
This works for me: dbfiddle.uk/… - VBoka
Odd, it's the same. Any idea what could cause the above not to work? the query works if I delete the WHERE part of the final select - NickyLarson
How much data do you have ? You do not say that it does not work, you explain that it takes too much time ? - VBoka
just over 72 thousand lines. If I delete the WHERE part of the statement it takes approximately 7 to 11 seconds to execute. with the query as it is with the WHERE, it doesn't error, it just executes forever (I've left it for 30 mins and it was still executing) - NickyLarson
Often OFFSET/FETCH is used for pagination. - Gordon Linoff

1 Answers

1
votes

Would TOP be something you need:

SELECT  top 3 t.id
         , t.age
         , t.name
         , t.surname
         , t2.workplace
FROM test t
left join test2 t2 on t.id = t2.id

Here is a demo:

DEMO

And how about this:

SELECT   t.id
         , t.age
         , t.name
         , t.surname
         , t2.workplace
FROM test t
left join test2 t2 on t.id = t2.id
ORDER BY t.id
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

DEMO