Window functions side by side

A window function works across rows but keeps every row, adding a new column. Pick a card to see it below on the same books and members.

• ROW_NUMBER ROW_NUMBER() OVER (ORDER BY year) A different number for every row: 1, 2, 3, 4. The first or latest row per group; removing duplicates.
• RANK RANK() OVER (ORDER BY loans DESC) Ties share a place, then a gap: 1, 2, 2, 4. League tables and leaderboards.
• DENSE_RANK DENSE_RANK() OVER (ORDER BY loans DESC) Ties share a place, no gap: 1, 2, 2, 3. "The 2nd highest value", counting distinct places.
• PARTITION BY OVER (PARTITION BY genre ORDER BY year) Starts the count again for each group. Rankings or numbers within each genre, team or month.
• Running total SUM(pages) OVER (ORDER BY year) Each row adds itself to every row before it. Totals so far: sales to date, pages read so far.
• Running total per group SUM(pages) OVER (PARTITION BY genre ORDER BY year) A separate running total for each group. Year-to-date per region, per product.

SELECT title, year, 
  ROW_NUMBER() OVER (ORDER BY year) AS position
FROM books;