RANK()
The
To demonstrate
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
Notice two things that happen in the
- The ranking provided is the same for every unique instance of salary.
- 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