RANK()

The RANK() window function returns the rank of the row based on the ORDER BY clause in comparison to the rows. It is very similar to the ROW_NUMBER() ROW_NUMBER function and in some cases, it may return the same result. The only difference is that the RANK function returns a rank by evaluating unique values and returning the same rank for every unique value while maintaining the position/sequence of the ordering.

To demonstrate RANK() and in particular, its difference to ROW_NUMBER() , lets implement the same query using both functions

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
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

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

We can see that salary_order and salary_rank are similar but differ when the salary value is the same.

Notice two things that happen in the salary_rank output.

1. The ranking provided is the same for every unique instance of salary.
2. The ranking position is preserved. It returns the rank of the current row based on how many rows were before it.

In the next section, we look at DENSE_RANK() which is similar to RANK but does not preserve ranking position.