Return row of every n'th record
How can I return every nth record from a sub query based on a numerical parameter that I supply?
For example, I may have the following query:
SELECT
Id,
Key
FROM DataTable
WHERE CustomerId = 1234
ORDER BY Key
e.g.
The subquery result may look like the following:
Row Id Key
1 1 A3231
2 43 C1212
3 243 E1232
4 765 G1232
5 2432 E2325
...
90 3193 F2312
If I pass in the number 30, and the sub query result set contained 90 records, I would recieve the 30th, 60th, and 90th row.
If I pass in the number 40, and the result set contained 90 records, I would recieve the 40th and 80th row.
As a side note, for background information, this is being used to capture the key/id of every nth record for a paging control.
This is where ROW_NUMBER
can help. It requires an order-by clause but this is okay because an order-by is present (and required to guarantee a particular order).
SELECT t.id, t.key
FROM
(
SELECT id, key, ROW_NUMBER() OVER (ORDER BY key) AS rownum
FROM datatable
) AS t
WHERE t.rownum % 30 = 0 -- or % 40 etc
ORDER BY t.key
You don't have to use row number, any integer field will do. On most tables we have an autonumber field. For simplicity, I will call it ID. To display every 13th record:
Select ID, LastName, FirstName FROM Users WHERE ID%13 = 0 ORDER BY ID
Select B.name
from (Select row_number() over (order by ordering) as row_num,
A.name
from (Select name,'P1' as ordering
from [dbo].[every_2nd_record]
) A
) B
where B.row_num%2=0