Window Functions: ROW_NUMBER and RANK

A window function calculates a value for each row, by looking at a group of related rows. Unlike GROUP BY, it does not combine the rows into one. Every row stays in the result.

You write a window function with OVER (...).


Syntax

SELECT column,
       function() OVER (PARTITION BY column ORDER BY column)
FROM table_name;
  • ORDER BY inside OVER sets the order the function counts in.
  • PARTITION BY splits the rows into groups. The function starts again for each group.

Example 1: Number the Rows with ROW_NUMBER()

SELECT name, age,
       ROW_NUMBER() OVER (
         ORDER BY age DESC, name
       ) AS position
FROM employees
WHERE department = 'Finance';

Output:

 name  | age | position 
-------+-----+----------
 Jack  |  33 |        1
 Paul  |  32 |        2
 Grace |  31 |        3
 Steve |  27 |        4
(4 rows)

Each Finance employee gets a number, from the oldest (1) to the youngest.


Example 2: Ties with RANK()

RANK() gives the same number to rows that tie, and then skips numbers:

SELECT name, age,
       RANK() OVER (ORDER BY age DESC) AS age_rank
FROM employees
WHERE age >= 31;

Output:

  name  | age | age_rank 
--------+-----+----------
 Jack   |  33 |        1
 Thomas |  33 |        1
 Bob    |  32 |        3
 Paul   |  32 |        3
 Grace  |  31 |        5
 Tina   |  31 |        5
(6 rows)

Jack and Thomas are both 33, so both are rank 1. The next rank is 3, because two rows came before it.


Example 3: Start Again for Each Group with PARTITION BY

SELECT department, name, age,
       RANK() OVER (
         PARTITION BY department ORDER BY age DESC
       ) AS rank_in_dept
FROM employees
WHERE department IN ('HR', 'Marketing')
ORDER BY department, rank_in_dept;

Output:

 department | name  | age | rank_in_dept 
------------+-------+-----+--------------
 HR         | Alice |  28 |            1
 HR         | Anna  |  24 |            2
 Marketing  | David |  30 |            1
 Marketing  | Ryan  |  29 |            2
(4 rows)

The ranking starts again at 1 for each department.


Tip

Window functions also work with aggregates. For example, AVG(age) OVER (PARTITION BY department) shows each row with the average age of its department.

In the exercise, number the employees inside each department.