Рейтинги: ROW_NUMBER, RANK і DENSE_RANK

Rankings: ROW_NUMBER, RANK and DENSE_RANK

Beyond aggregating, window functions shine at ordering and ranking. There are functions designed specifically to work inside windows (OVER() becomes mandatory) that assign sequential numbers or ranking positions.

To make them work against a criterion, we have to use the ORDER BY clause inside OVER().

The ranking trio

1. ROW_NUMBER(): Assigns a plain sequential number (1, 2, 3...) to every row, breaking ties arbitrarily.
2. RANK(): Produces the "Olympics" effect: 1, 2, 2, 4. It leaves a gap (the "3" is skipped) and assigns the same rank in case of ties.
3. DENSE_RANK(): Densifies ranks: 1, 2, 2, 3. There is never a gap after a tie.

SELECT
  product_name,
  price,
  RANK() OVER(ORDER BY price DESC) AS price_rank
FROM products;

By combining PARTITION BY and ORDER BY together inside OVER() we can get "the top 3 for each category"!