ROW_NUMBER

ROW_NUMBER() OVER ([PARTITION BY column_1, column_2, …] [ORDER BY column_3, column_4, …])

Example

SELECT name,
       age,
       ROW_NUMBER() OVER(ORDER BY age DESC) AS RowNum
FROM students

Output

| name  | age | RowNum |
|-------|-----|--------|
| John  |  22 |      1 |
| Alice |  20 |      2 |
| Bob   |  19 |      3 |

Explanation

In the SQL provided, ROW_NUMBER() is a window function that assigns a unique, sequential integer to each row within the partition of the result set that was defined by the OVER() clause. The OVER() clause has an ORDER BY statement which orders the rows by age in descending order before the ROW_NUMBER() function is applied. This results in the oldest student getting the row number 1, and so forth.

ROW_NUMBER() OVER ([PARTITION BY column_1, column_2, …, column_n] [ORDER BY column_1, column_2, …, column_n [ASC|DESC]]);

Example

WITH sales AS (
  SELECT 'A' AS salesperson, 100 AS revenue
  UNION ALL SELECT 'A', 200
  UNION ALL SELECT 'B', 150
  UNION ALL SELECT 'B', 250
  UNION ALL SELECT 'B', 300
  UNION ALL SELECT 'C', 400
)
SELECT salesperson, revenue, ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY revenue DESC)
FROM sales;

Output

| salesperson | revenue | row_number |
|-------------|---------|------------|
| A           | 200     | 1          |
| A           | 100     | 2          |
| B           | 300     | 1          |
| B           | 250     | 2          |
| B           | 150     | 3          |
| C           | 400     | 1          |

Explanation

The example code defines a sales table and then selects all rows from this table. For each salesperson, it assigns a unique row number based on the revenue in descending order; highest revenue gets the row number 1. This window function helps in ranking rows within a partition.

ROW_NUMBER( ) OVER ( [ PARTITION BY value_expression , … [ n ] ] ORDER BY clause )

partition by value_expression: This parameter defines the column by which the result set will be divided into partitions. Each partition is assigned a unique row number. It can contain one or more columns.
order by clause: This parameter defines the criteria to order rows in a partition. The first row starts with 1 and the number increments with each subsequent row. Several column names can be included separated by commas, and each can be sorted in ascending or descending order.
n: An optional parameter. If applied, denotes that, in the result set, the row_number for each partition should reset and start from 1 after ‘n’ number of rows.

Example

SELECT
    ROW_NUMBER() OVER(ORDER BY Salary DESC) AS Row,
    FirstName,
    LastName,
    Salary
FROM
    Employees

Output

Row	FirstName	LastName	Salary
1	John	Doe	100000
2	Jane	Doe	80000
3	James	Smith	50000
4	Mary	Johnson	35000

Explanation

In the provided example, the ROW_NUMBER() function enumerates the rows in the order of the Salary column in descending order. Each row in the results gets a unique number. This is useful for tasks such as ranking results, generating row numbers, etc. The function operates on a set of rows defined by the OVER clause, which specifies the column by which the result set should be ordered. In case of ties, the function arbitrarily assigns a rank.

ROW_NUMBER() OVER ([PARTITION BY column_1, column_2,…] ORDER BY column_3, column_4,…)

Example

SELECT emp_id, emp_name, ROW_NUMBER() OVER(ORDER BY emp_name) AS RowNum
FROM Employees

Output

EMP_ID | EMP_NAME | ROWNUM
-------|----------|-------
1      | Anna     | 1
2      | Bill     | 2
3      | Charlie  | 3
4      | Dianne   | 4

Explanation

The ROW_NUMBER() function assigns a unique row number to each row, which is ordered by emp_name in ascending order. The row number starts at 1 for the first row in each partition.