Following a question made me by another developer, I write here (mostly to remember it) how to read a specific row in a table using an arbitrary ordering method.
In TSQL we have just the SELECT TOP to decide a number of records to read but to get the Nth row of a table from the SQL 2005 version we have a nice function called ROW_NUMBER. I’ve used the dear old Pubs database coming as case study with SQL until version 2000.
This is how to build the query, to number the Authors table:
SELECT [au_id]
,[au_lname]
,[au_fname]
,[phone]
,[address]
,[city]
,[state]
,[zip]
,[contract]
,[brthdate]
, row_number() over( order by au_lname) as rnum
FROM [pubs].[dbo].[authors]
The rnum column, contains a progressive numbering for the rows based on the author last name. Using Row_Number we can also number groups of rows, for instance:
, Row_Number() Over(partition by [state] Order by [state]) as RNum
With this syntax we get rows numbered from 1 to n for each state in the list.
To get the Nth record, however, we have to surround our query with another one like in the code below:
SELECT * from (
SELECT [au_id]
,[au_lname]
,[au_fname]
,[phone]
,[address]
,[city]
,[state]
,[zip]
,[contract]
,[brthdate]
, row_number() over( order by au_lname) as rnum
FROM [pubs].[dbo].[authors]) aut
WHERE aut.rnum = 15