SQL Window Functions: Calculations Across Rows

Window functions allow you to perform calculations across a set of related rows without collapsing those rows into a single result. They are especially useful for rankings, running totals, and comparing a row with previous or next rows.

The key difference from GROUP BY is that GROUP BY combines rows, while a window function keeps the original rows.

Suppose we have:

We want to find each employee's salary rank within their department:

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

The result is:

We could add a regular ORDER BY to make the result easier to read:

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

Why not just use ORDER BY?

A regular: 

ORDER BY salary DESC

would simply sort all employees by salary.

It would not calculate a rank for each employee, and it would not independently rank employees within each department.

Simple mental model

Partition → separate the groups
Order → determine the ranking order
Rank → assign a position to each row
Result → keep all original rows