Window functions - rankings in the Library

Sometimes we want not just to retrieve rows, but also to number them or give them a rank within a group. That is what window functions are for.

ROW_NUMBER - a sequential number

ROW_NUMBER()

gives each row a sequential number according to the given ordering:

1SELECT tytul, cena,
2       ROW_NUMBER() OVER (ORDER BY cena) AS numer
3FROM ksiazki;

The cheapest book gets number 1, the next 2, and so on. The clause

OVER (ORDER BY ...)

tells it what to count by.

RANK - rank with ties

RANK()

works similarly, but books with the same price get the same rank, and the next rank is skipped (1, 2, 2, 4).

1SELECT tytul, cena,
2       RANK() OVER (ORDER BY cena DESC) AS ranga
3FROM ksiazki;

PARTITION BY - rankings within groups

With

PARTITION BY

we number separately within each group. This way we set up a ranking of books by price inside each category:

1SELECT tytul, kategoria_id, cena,
2       ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC) AS miejsce
3FROM ksiazki;

The counter resets for each category. Window functions are the Keeper's powerful spyglass - they let you compare and rank without losing the individual rows.