How to use ROW_NUMBER function properly in SQL Server?

In SQL Server, ROW_NUMER() is a common window function used to return a sequential number of a row within a partition of a result set, starting with 1 in each partition. Those numbers only exist temporarily with the query results, not with the table.

In other words, you are numbering the result of the query. It might be similar to RANK, but the output number of ROW_NUMBER is sequential, while RANK assigns a constant value to each row in a group and leaves a gap between the group records.

General syntax for ROW_NUMBER():

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY column_1 , ... [ n ] ] ORDER BY column_2 )  

PARTITION BY argument is optional.

When you add this argument, the function will divide the results into multiple different sets, grouped by the value from the column col_1. Each set of the result will have a different set of row numbers.

For example, we have three sets of results, the function will number the results from the first set, then start over for the second set, and start over again for the third set.

If PARTITION BY is not specified, the ROW_NUMBER function will treat all rows from the query result as one group.

You can also use col_1 for the ORDER BY clause.

Let’s see this in practices:

We have a table orders:

table_no	total_amount
17	102
12	828
17	679
13	788
9	122
1	90
17	88
1	313
12	222

Some table numbers have more than one record, each record has different a total_amount value.

Now we use the ROW_NUMBER function to number the results of the table orders, grouped by table_no and with the decreasing total_amount:

SELECT 
  ROW_NUMBER() OVER(PARTITION BY table_no ORDER BY total_amount DESC) 
    AS row_no,
  table_no, total_amount
FROM orders;

The result will be:

row_no	table_no	total_amount
1	1	313
2	1	90
1	9	122
1	12	828
2	12	222
1	13	788
1	17	679
2	17	102
3	17	88

As you can see, ROW_NUMBER divided the query results into 5 groups with 5 different sets of row numbers, corresponding to 5 value of table_no: 1, 9, 12, 13, 17.

And when we don’t specify the PARTITION BY argument:

SELECT 
  ROW_NUMBER() OVER(ORDER BY total_amount DESC) 
    AS row_no,
  table_no, total_amount
FROM orders;

The result will be:

row_no	table_no	total_amount
1	12	828
2	13	788
3	17	679
4	1	313
5	12	222
6	9	122
7	17	102
8	1	90
9	17	88

So you can see the difference, all records are treated the same as in one single group.

Need a good GUI Client to work with MS SQL Server? TablePlus provides a modern, native tool with intuitive UI to manage multiple relational databases in cluding SQL Server, MySQL, PostgreSQL, SQLite, Oracle…