Include row numbers in your SQL SELECT results
The first of a series of posts introducing handy functions built into SQL Server (2005 and above) to help you include interesting metadata about your SELECT statement's results.
If you'd like to include the row number of the items as they appear in your SELECT result set then the
Row_Number function does exactly that. For example:
Select SortName, Row_Number() Over (Order By Forename) As Row From T_Person Where Surname = 'Lawson'
Would result in:
SortName Row ------------------------- ------ Lawson, Aaliyah 1 Lawson, Aaron 2 Lawson, Adam 3 Lawson, Adrian 4 Lawson, Alexa 5 Lawson, Alexander 6 Lawson, Ashley 7 Lawson, Braden 8
So regardless of a record's position in the source table, the
Row_Number is its position in the result set, after all ordering and grouping has been applied.
We can go further than this and use the row number to solve a couple of other problems:
Returning a subset of rows
If you need to select rows 10-20 (perhaps for pagination functionality) then you could do that using the row_number:
Select SortName, Row From ( Select SortName, Row_Number() Over (Order By Forename) As Row From T_Person ) Results Where Row Between 10 And 20
Returning the Nth row
If you wanted to return say the 5th row from our result set, you can do that using row_number too:
Select SortName, Row From ( Select SortName, Row_Number() Over (Order By Forename) As Row From T_Person ) Results Where Row = 5
Leave a Reply