SQL and MySQL course Β· Module 10: Transactions and advanced SQL
Window functions - rankings in the Library
In this lesson3
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:
SELECT tytul, cena,
ROW_NUMBER() OVER (ORDER BY cena) AS numer
FROM 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).
SELECT tytul, cena,
RANK() OVER (ORDER BY cena DESC) AS ranga
FROM 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:
SELECT tytul, kategoria_id, cena,
ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC) AS miejsce
FROM 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.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What is the ROW_NUMBER() OVER (ORDER BY ...) window function used for?
2. What is the OVER clause in a window function used for?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Click in order
Click the elements in the order in which we build a window function that numbers rows:
- Code editor
Write a query that retrieves tytul and cena from the ksiazki table, and additionally adds a column aliased as numer containing the result of ROW_NUMBER() OVER (ORDER BY cena). Use the word AS to name the numer column.
- Horizontal ordering
Arrange in one line a window function that numbers rows by price descending:
- Code editor
Write a query that retrieves tytul and cena from the ksiazki table, and additionally adds a column aliased as ranga containing the result of RANK() OVER (ORDER BY cena DESC). Use the word AS to name the ranga column.