Window Functions: Ranking

GROUP BY collapses rows into one per group. A window function computes across a set of related rows without collapsing them — each row keeps its own identity and gains a calculated value.

Syntax

function() OVER (PARTITION BY group_column ORDER BY sort_column)

• OVER (...) makes it a window function.
• PARTITION BY splits rows into groups (like GROUP BY, but without collapsing). Leave it out to treat all rows as one group.
• ORDER BY sets the order within each partition.

Ranking rows

SELECT name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC)                        AS overall,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS in_department
FROM employees
ORDER BY department, in_department;

+-------+-------------+--------+---------+---------------+
| name  | department  | salary | overall | in_department |
+-------+-------------+--------+---------+---------------+
| Sofia | Engineering | 120000 |       1 |             1 |
| Priya | Engineering |  98000 |       2 |             2 |
| Omar  | Engineering |  95000 |       3 |             3 |
| Chen  | Sales       |  70000 |       4 |             1 |
| Lucia | Sales       |  64000 |       5 |             2 |
| Kofi  | Support     |  52000 |       6 |             1 |
+-------+-------------+--------+---------+---------------+

Every employee is still listed; each gets their position overall and within their department.

ROW_NUMBER vs RANK vs DENSE_RANK

They differ only when values tie:

-- standalone
CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES ('Ada', 90), ('Linus', 85), ('Grace', 90), ('Alan', 80);

SELECT player, points,
  ROW_NUMBER() OVER (ORDER BY points DESC, player) AS row_number,
  RANK()       OVER (ORDER BY points DESC)         AS rank,
  DENSE_RANK() OVER (ORDER BY points DESC)         AS dense_rank
FROM scores
ORDER BY points DESC, player;

+--------+--------+------------+------+------------+
| player | points | row_number | rank | dense_rank |
+--------+--------+------------+------+------------+
| Ada    |     90 |          1 |    1 |          1 |
| Grace  |     90 |          2 |    1 |          1 |
| Linus  |     85 |          3 |    3 |          2 |
| Alan   |     80 |          4 |    4 |          3 |
+--------+--------+------------+------+------------+

• ROW_NUMBER — always unique (ties broken arbitrarily unless you add a tiebreaker, as here with player).
• RANK — ties share a rank, then it skips (1, 1, 3).
• DENSE_RANK — ties share a rank, with no gaps (1, 1, 2).

Top N per group

"The highest-paid employee in each department" — a very common question that GROUP BY alone can't answer cleanly (it can give the top salary, but not the rest of that person's row):

WITH ranked AS (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn = 1
ORDER BY department;

+-------------+-------+--------+
| department  | name  | salary |
+-------------+-------+--------+
| Engineering | Sofia | 120000 |
| Sales       | Chen  |  70000 |
| Support     | Kofi  |  52000 |
+-------------+-------+--------+

Window functions are computed after WHERE, so you can't filter on them directly — hence the CTE. Change rn = 1 to rn <= 3 for the top three per group.

Practice

For each order, rank its items from most to least expensive line total (quantity * unit_price).

SELECT order_id, product_id, quantity * unit_price AS line_total,
  RANK() OVER (PARTITION BY order_id ORDER BY quantity * unit_price DESC) AS rank_in_order
FROM order_items
ORDER BY order_id, rank_in_order;

+----------+------------+------------+---------------+
| order_id | product_id | line_total | rank_in_order |
+----------+------------+------------+---------------+
|        1 |          1 |         89 |             1 |
|        1 |          2 |       71.0 |             2 |
|        2 |          3 |     249.99 |             1 |
|        3 |          5 |        199 |             1 |
|        3 |          2 |       35.5 |             2 |
|        4 |          3 |     499.98 |             1 |
|        4 |          4 |         84 |             2 |
|        5 |          6 |       59.9 |             1 |
|        6 |          1 |         89 |             1 |
|        7 |          5 |        199 |             1 |
|        7 |          4 |         42 |             2 |
|        8 |          6 |      179.7 |             1 |
+----------+------------+------------+---------------+