# 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`.

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

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