ROW_NUMBER

The ROW_NUMBER() window function is one of the most commonly used window function. It returns unique integers for each row in the resulting order based on some ordering parameter. ROW_NUMBER() typically has an ORDER BY specification in the ROW_NUMBER() argument that determines the unique numbers which start at 1 and increase by 1.

Sample Employee Table

Suppose we have an employees table that we can query the employee_id, first_name, last_name, salary. The output looks like below:

select employee_id, first_name, last_name, salary from employees limit 20;

|  employee_id | first_name  | last_name |  salary
|--------------+-------------+-----------+----------
|            1 | John        | Smith     | 75000.00
|            2 | Sarah       | Johnson   | 82000.00
|            3 | Michael     | Brown     | 95000.00
|            4 | Emily       | Davis     | 68000.00
|            5 | David       | Wilson    | 71000.00
|            6 | Lisa        | Anderson  | 59000.00
|            7 | Robert      | Taylor    | 89000.00
|            8 | Jennifer    | Martinez  | 64000.00
|            9 | Christopher | Garcia    | 77000.00
|           10 | Amanda      | Rodriguez | 72000.00
|           11 | James       | Hernandez | 66000.00
|           12 | Michelle    | Lopez     |
|           13 | Kevin       | Gonzalez  | 98000.00
|           14 | Rachel      | Perez     | 61000.00
|           15 | Brian       | Turner    | 55000.00
|           16 | Nicole      | Phillips  | 83000.00
|           17 | Daniel      | Campbell  | 70000.00
|           18 | Stephanie   | Parker    | 67000.00
|           19 | Matthew     | Evans     | 91000.00
|           20 | Lauren      | Edwards   | 58000.00
(20 rows)

Find the nth salaried employeed

Now suppose we are interested in knowing the nth largest salary and what employee earns that much. The easiest approach to finding this is to use ROW_NUMBER() which will provide a rank. The actual query looks like below:

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

| employee_id | first_name  | last_name  |  salary  | salary_rank
|-------------+-------------+------------+----------+-------------
|          13 | Kevin       | Gonzalez   | 98000.00 |           1
|           3 | Michael     | Brown      | 95000.00 |           2
|          35 | Gregory     | Peterson   | 94000.00 |           3
|          45 | Adam        | Wood       | 92000.00 |           4
|          19 | Matthew     | Evans      | 91000.00 |           5
|           7 | Robert      | Taylor     | 89000.00 |           6
|          30 | Samantha    | Richardson | 88000.00 |           7
|          43 | Scott       | Price      | 87000.00 |           8
|          24 | Kimberly    | Morris     | 86000.00 |           9
|          38 | Vanessa     | James      | 85000.00 |          10
|          50 | Elizabeth   | Perry      | 83000.00 |          11
|          16 | Nicole      | Phillips   | 83000.00 |          12
|           2 | Sarah       | Johnson    | 82000.00 |          13
|          27 | Justin      | Bailey     | 81000.00 |          14
|          33 | Nicholas    | Ward       | 79000.00 |          15
|          41 | Tyler       | Kelly      | 78000.00 |          16
|           9 | Christopher | Garcia     | 77000.00 |          17
|          26 | Ashley      | Cook       | 76000.00 |          18
|           1 | John        | Smith      | 75000.00 |          19
|          47 | Benjamin    | Ross       | 75000.00 |          20
|          21 | Anthony     | Collins    | 74000.00 |          21
|          31 | Jonathan    | Cox        | 73000.00 |          22
|          10 | Amanda      | Rodriguez  | 72000.00 |          23
|          37 | Patrick     | Ramirez    | 71000.00 |          24
|           5 | David       | Wilson     | 71000.00 |          25
|          17 | Daniel      | Campbell   | 70000.00 |          26
|          22 | Megan       | Stewart    | 69000.00 |          27
|           4 | Emily       | Davis      | 68000.00 |          28
|          46 | Melissa     | Barnes     | 68000.00 |          29
|          36 | Tiffany     | Gray       | 67000.00 |          30
|          18 | Stephanie   | Parker     | 67000.00 |          31
|          11 | James       | Hernandez  | 66000.00 |          32
|          49 | Jacob       | Jenkins    | 66000.00 |          33
|          28 | Brittany    | Rivera     | 65000.00 |          34
|           8 | Jennifer    | Martinez   | 64000.00 |          35
|          42 | Andrea      | Sanders    | 64000.00 |          36
|          25 | Ryan        | Reed       | 63000.00 |          37
|          34 | Crystal     | Torres     | 62000.00 |          38
|          44 | Rebecca     | Bennett    | 61000.00 |          39
|          14 | Rachel      | Perez      | 61000.00 |          40
|          39 | Eric        | Watson     | 60000.00 |          41
|           6 | Lisa        | Anderson   | 59000.00 |          42
|          29 | Alexander   | Cooper     | 59000.00 |          43
|          20 | Lauren      | Edwards    | 58000.00 |          44
|          15 | Brian       | Turner     | 55000.00 |          45
(45 rows)

In this case, notice that we can now use the salary rank to get the salary at a particular position.

Duplicates with ROW_NUMBER()

If you have been observing, you will notice that there are employees that make the same amount by are ranked different. For example employee_ids $4$ and $46$ both earn the same amount. This is because ROW_NUMBER() only does increments of 1 regardless of the evaluation condition. In the coming sessions we will look at alternatives like RANK() and DENSE_RANK() which will fix some of these issues. You can use the summary below to think through these.

	Function	Handles Ties	Sequential Numbers
	ROW_NUMBER()	Assigns different numbers	Always 1,2,3,4...
	RANK()	Same rank for ties	May skip numbers (1,2,2,4...)
	DENSE_RANK()	Same rank for ties	No gaps (1,2,2,3...)

In the next section, we look at the PARTITION BY window function.