Ranking rows

ROW_NUMBER, RANK, and DENSE_RANK.

Number and rank

Ranking functions need an ORDER BY *inside* the window:

- ROW_NUMBER() — 1,2,3… a unique position - RANK() — ties share a rank, then it skips (1,1,3) - DENSE_RANK() — ties share a rank, no gaps (1,1,2)

Most expensive book per genre — number them within each genre, priciest first:

SELECT title, genre, price,
       RANK() OVER (PARTITION BY genre ORDER BY price DESC) AS price_rank
FROM books;