DENSE_RANK()

The DENSE_RANK function returns a unique rank for the row based on the unique value of the column defined in the partition. Another way to look at DENSE_RANK() is that it returns rank as how many unique values come before the one in the current row.

To demonstrate the DENSE_RANK, we compare the ROW_NUMBER, RANK, and DENSE_RANK functions in the query below to highlight the differences in their output.

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

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

Examining the output, we can see that the salary_dense_rank has a sequential ordering, without skipping any rank even when one or more observations have the same salary.