Cheatsheet Pages / SQL Cheatsheet
How to rank rows inside groups in SQL — RANK() OVER (PARTITION BY ...)
You want a ranking inside each group, such as salary rank per department, without collapsing the rows.
Every example runs against the cheatsheet's sample tables — departments(id, name), employees(id, name, dept_id, salary, manager_id, hired_on) and orders(id, emp_id, amount, ordered_on) — and was executed and verified against SQLite, so you can paste it in and get the same result.
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees;
-- In dept 1: Alice 1, Bob 2, Carol 3; in dept 2: David 1, Eve 2
-- GROUP BY collapses rows; window functions keep every row and attach a rank — that's the key difference
-- Dialect: MySQL 8.0+ / PostgreSQL / SQLite 3.25+. Older MySQL can't do this