How to Number Rows in SQL

Problem:

You would like to give each row in the result table a separate number.

Example:

Our database has a table named furniture with data in the following columns: code (primary key) and name.

The furniture table stores the name of pieces of furniture that we want to number.

Solution:

SELECT 
  ROW_NUMBER() OVER() AS num_row,
  code, 
  name
FROM furniture;

The query returns the row number of each piece of furniture along with its name and code:

Note that the names of the pieces of furniture are not sorted.

Discussion:

If you’d like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function. If you don't pass in any arguments to OVER, the numbering of rows will not be sorted according to any column. Thus, the order of the displayed rows will be non-deterministic; in most cases, it is the order in which the records were inserted into the table. The numbering of rows starts at 1. In our example, each record has a number from 1 to 6.

We can also assign row numbers using a column. In the example below, we number records sorted by name. We do this by passing in that argument to OVER along with ORDER BY(it sorts records according to the name column):

SELECT 
  ROW_NUMBER() OVER(ORDER BY name) AS num_row,
  name, 
  code
FROM furniture;

As you look at the result set, you see the sorted rows with their numbers:

The numbering provided by ROW_NUMBER() is independent of the order of rows in the result table. In the example below, we number records using the sorted column name (OVER(ORDER BY name)), but we display records in the result set according to another column (in our example, ORDER BY code).

SELECT 
  ROW_NUMBER() OVER(ORDER BY name) AS num_row,
  name, 
  code
FROM furniture
ORDER BY code;

The query returned the number each row, but these numbers are not in ascending order, because records are sorted according column code:

The default order of the sorted rows is ascending, but you can also sort in descending order using the DESC keyword after the name of the column by which you want to sort:

SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS num_row,
  name, 
  code
FROM furniture
ORDER BY code DESC;

The query above returned the result set: