Make results pretty with TSQL row_number()

You can use row_number() to sort and number the results.  This can be useful for creating a view that shows a custom sorting order.  But first, here are some different ways you can work with this function.


Here is a basic example of how to use row_number().  In this case we decided to rank employees based on their lastname in alphabetical order. In a practical situation, we may want to achieve numbering like this.

USE TSQL2012
SELECT row_number() OVER (ORDER BY lastname) AS employeerank, lastname, firstname, title, titleofcourtesy
FROM HR.Employees
GO

Here is our output.  Notice we have created a new column on the fly, called employeerank, and each row is numbered.

rownumber_001

The OVER clause contains the instructions for ordering the results.  Without ordering, there is no point in trying to use the row_number() function.  If you try to remove the OVER clause, the query will fail and you will get an error like this:

Msg 10753, Level 15, State 3, Line 2
The function 'row_number' must have an OVER clause.

On another note, you can remove the AS employeerank clause.  This just means the column won’t have a heading.

Let’s move onto something more advanced, the PARTITION BY clause.  The partition by clause can be added along side the order by clause when using row_number().  For example:

USE TSQL2012
SELECT row_number() OVER (PARTITION BY title ORDER BY lastname) AS EmployeeRank, lastname, firstname, title, titleofcourtesy
FROM HR.Employees
GO

You get something like this:

rownumber_002

Notice how the row numbering restarts every time the title changes?  The results are partitioned by the title column, and within each partition the row numbering and ordering by lastname still takes place as it did previously.

That’s it for the row_number() function.  We will talk about rank and dense rank separately because row number does not address unique values when ordering.