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.

Row_Number()

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