Schema and sample data
| emp_id | name | salary |
|---|---|---|
| 1 | Ava Torres | 95000.00 |
| 2 | Ben Ncube | 82000.00 |
| 3 | Cara Singh | 82000.00 |
| 4 | Deng Liu | 71000.00 |
| 5 | Emma Rossi | 64000.00 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (emp_id, name, salary) VALUES
(1, 'Ava Torres', 95000.00),
(2, 'Ben Ncube', 82000.00),
(3, 'Cara Singh', 82000.00),
(4, 'Deng Liu', 71000.00),
(5, 'Emma Rossi', 64000.00);From the Employees table, return each employee's name, salary and a ranking column called salary_rank that ranks employees from the highest paid to the lowest paid. Use RANK() so that two employees on the same salary share a rank. Sort the output by salary_rank ascending, then by name.
Expected result
| name | salary | salary_rank |
|---|---|---|
| Ava Torres | 95000.00 | 1 |
| Ben Ncube | 82000.00 | 2 |
| Cara Singh | 82000.00 | 2 |
| Deng Liu | 71000.00 | 4 |
| Emma Rossi | 64000.00 | 5 |
Show hint
This is a window function. Add RANK() OVER (ORDER BY salary DESC) as an extra column. The OVER clause defines the order in which rows are ranked without collapsing them into groups, so every employee row still comes back.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM Employees
ORDER BY salary_rank, name;RANK() OVER (ORDER BY salary DESC) is a window function: it assigns a number to every row based on the ordering inside the OVER clause, but unlike GROUP BY it does not fold rows together, so all five employees are still returned. The highest salary (95000) gets rank 1, the next distinct salary gets the next rank, and so on.
Ben Ncube and Cara Singh both earn 82000, so they tie and RANK() gives them the same rank of 2. The key behaviour to notice is what happens next: RANK() leaves a gap. Because two rows consumed positions 2 and 3, the following employee (Deng Liu) is ranked 4, not 3. The rank of a row equals one plus the number of rows that strictly outrank it. See the RANK function reference for more detail.
Contrast this with the two sibling functions. DENSE_RANK() also gives tied rows the same rank but does not leave a gap, so the sequence here would be 1, 2, 2, 3, 4. ROW_NUMBER() ignores ties entirely and hands every row a unique number (1, 2, 3, 4, 5), breaking the tie arbitrarily. A common mistake is reaching for ROW_NUMBER() when the question asks for a ranking, which silently gives one of the tied employees a better position than the other. For a side by side breakdown, read RANK vs DENSE_RANK vs ROW_NUMBER.