How to select a specific range from first row with each id MSSQL

Question

I have created a table in html to display first rows of each id and allow user to select specific range they want to see, but not sure how I can write it in query.

With results as 
 (
   i.*, 
   ROW_NUMBER() OVER (PARTITION BY i.ID ORDER BY I.ID DESC) AS [RN] 
   FROM HolidayList AS I
   INNER JOIN (
      SELECT ID, MIN(CreateDate) FROM HolidayList GROUP BY ID
   )
   AS j ON i.ID = j.ID AND i.CreateDate = j.CreateDate
 )
 SELECT * FROM results WHERE [RN] = 1;

UPDATED

I have tried to inject two ROW_NUMBER(), but what I received is row 10 and row 25 and they are first rows of some specific id.

 With results as 
     (
       i.*, 
       ROW_NUMBER() OVER (PARTITION BY i.ID ORDER BY I.ID DESC) AS [RN], 
       ROW_NUMBER() OVER (ORDER BY I.ID) AS [R]
       FROM HolidayList AS I
       INNER JOIN (
          SELECT ID, MIN(CreateDate) FROM HolidayList GROUP BY ID
       )
       AS j ON i.ID = j.ID AND i.CreateDate = j.CreateDate
     )
     SELECT * FROM results WHERE [RN] = 1 AND BETWEEN [R]>10 AND [R]<25

What I am really looking for is select a specific range from all the first row for each id.

FINAL

Thank to McGlothlin, finally I solve it. What I need is a nested CTE.

With First_CTE as 
         (
           i.*, 
           ROW_NUMBER() OVER (PARTITION BY i.ID ORDER BY I.ID DESC) AS [RN]
           FROM HolidayList AS I
           INNER JOIN (
              SELECT ID, MIN(CreateDate) FROM HolidayList GROUP BY ID
           )
           AS j ON i.ID = j.ID AND i.CreateDate = j.CreateDate
         ),
         results AS 
          (
            SELECT k.*,  
            ROW_NUMBER() OVER (ORDER BY k.ID DESC) AS [R] 
            FROM First_CTE AS k WHERE k.[RN] = 1
          )
          SELECT * FROM results WHERE [R]>10 AND [R]<25

You tagged `mysql` but `mysql` doesn't support CTEs. https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — McGlothlin, Jul 06 '17 at 21:27
The `WHERE` clause on your second posted query should be `WHERE [RN] = 1 AND [RN] BETWEEN ([R]>10 AND [R]<25)`. — BJones, Jul 06 '17 at 21:36
@McGlothlin Thanks you, I didn't know `mysql` doesn't support CTEs. Nice reading. — roger, Jul 06 '17 at 21:51
@bjones the outcome for the second code doesn't match with what I have imaged it would give. I thought it would produce all the first rows and then from the first row I can select row 10 to row 25 from the first rows. — roger, Jul 06 '17 at 21:56
@Kim.L By I'm glad I was able to help. Please consider accepting the answer if you believe it adequately answered your question. — McGlothlin, Jul 07 '17 at 01:39

McGlothlin · Accepted Answer · 2017-07-06T22:34:06.920

2

The problem lies in this line:

ROW_NUMBER() OVER I.ID AS [R]

I think what you meant was:

ROW_NUMBER() OVER (ORDER BY I.ID) AS [R]

This is assuming you want to give it a row count over every row that is returned in the CTE. The ROW_NUMBER() function requires an ORDER BY to be specified, and the OVER clause would still require parentheses if you were using a function like SUM that could be used without an ORDER BY.

Edit: Based on your comment, it sounds like what you're looking for is an OFFSET. In that case you would remove the second ROW_NUMBER and include something like this:

ORDER BY ID OFFSET 10 ROWS FETCH FIRST 15 ROWS ONLY

This returns rows 11 to 25, sorted by ID.

Since the OFFSET syntax doesn't work in SQL Server 2008, you should do something like this as your end result:

With results as 
    (
      i.*, 
      ROW_NUMBER() OVER (PARTITION BY i.ID ORDER BY I.ID DESC) AS [RN] 
      ROW_NUMBER() OVER (ORDER BY I.ID) AS [R]
      FROM HolidayList AS I
      INNER JOIN (
         SELECT ID, MIN(CreateDate) FROM HolidayList GROUP BY ID
      )
      AS j ON i.ID = j.ID AND i.CreateDate = j.CreateDate
    )
    SELECT *
    FROM results
    WHERE [RN] = 1
        AND [R] BETWEEN 11 AND 25

edited Jul 06 '17 at 22:34

answered Jul 06 '17 at 21:57

McGlothlin

2,059
1
15
28

Thanks McGlothlin, that helped with half the problem. It solved the error message, but differently to I had imaged it would give me. I thought I would get `from all the first rows select row 10 to row 25`, but the above code gave `each row is numbered and select first rows between row 10 and row 25`. English is not my first language, please correct me if I don't make sense. – roger Jul 06 '17 at 22:11
I am thinking out loud, but I have a feeling I may need to nest the above code to get all the first rows, then from there select all the rows. – roger Jul 06 '17 at 22:13
Updated answer accordingly. More info here: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx – McGlothlin Jul 06 '17 at 22:21
Oops... just noticed the `sql-server-2008` tag (how ironic). `OFFSET` was introduced in 2012. I'll update. – McGlothlin Jul 06 '17 at 22:32

How to select a specific range from first row with each id MSSQL

1 Answers1