SQL ROW_NUMBER()

The ROW_NUMBER() window function assigns a sequential integer (1,2,3…) to each row of a result set, starting with 1 for the first row of each partition.

Syntax

ROW_NUMBER() OVER (
  [PARTITION BY partition_expression,...]
  ORDER BY sort_expression [ASC | DESC], ...
)

• PARTITION BY is an optional parameter used to create partitions (groups of rows). If it is not used, ROW_NUMBER() will treat the result set as one partition. It is important to note that ROW_NUMBER() is applied to each partition separately; so the first row in a partition is assigned ‘1’, the second row is assigned ‘2’, and so on.
• ORDER BY defines the order within each partition. It is a mandatory parameter, because ROW_NUMBER() is order sensitive.
• ROW_NUMBER() can also be used for pagination, dividing the data set into discrete increments. For example, if a result set would have 100 rows, adding a WHERE clause would allow the user to selectively view only results 11-20.

Example

This function is useful when creating tables. The following example is using a dataset, customers, with entries for first_name, last_name, and city (where they live).

Using the following statement, the above information is pulled from the dataset customers. It is ordered alphabetically by their first name, then each row is assigned an integer starting with ‘1’.

SELECT
  ROW_NUMBER() OVER (
  ORDER BY first_name
  ) row_num,
  first_name,
  last_name,
  city
FROM
  customers;

Copy to clipboard
Copy to clipboard

This creates the following output. Notice that the above statement does not have a PARTITION BY parameter. This essentially creates just one partition, the result set.

For pagination, the function would be changed like so to only show the first 5 entries:

WITH customers AS (
  SELECT
    ROW_NUMBER() OVER (
      ORDER BY
        first_name
      ) row_num,
      first_name,
      last_name,
      city
  FROM
    customers
) SELECT
  first_name,
  last_name,
  city
FROM
  customers
WHERE
  row_num > 0 AND
  row_num <= 5;

Copy to clipboard
Copy to clipboard

Which would give the following output:

The ROW_NUMBER() function above could instead be modified like so to include partitions:

SELECT
  ROW_NUMBER() OVER (
   PARTITION BY city
   ORDER BY first_name
  ) row_num,
    first_name,
    last_name,
    city
FROM
  customers
ORDER BY
  city;

Copy to clipboard
Copy to clipboard

Above, the result set is partitioned by city. Each partition then has the ROW_NUMBER() function applied to it. Each city therefore has its own first row, assigned ‘1’; second row, assigned ‘2’; and so on.