Articles Cookbook
Back to list
Standard SQL

How to Number Rows in SQL

Database:

Operators:

ROW_NUMBER, OVER, ORDER BY, ASC, DESC

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.

codename
101bed
202sofa
333chair
123bookcase
235table
766desk

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:

num_rownamecode
1bed101
2sofa202
3chair333
4bookcase123
5table235
6desk766

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 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:

rownamecode
1bed101
2bookcase123
3chair333
4desk766
5sofa202
6table235

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:

rownamecode
1bed101
2bookcase123
5sofa202
6table235
3chair333
4desk766

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:

rownamecode
4desk766
3chair333
6table235
5sofa202
2bookcase123
1bed101

Recommended courses:

Recommended articles:

See also:

go to top