SQL and MySQL course Β· Module 10: Transactions and advanced SQL

Window functions - rankings in the Library

2 min read Β·
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. 1. What is the ROW_NUMBER() OVER (ORDER BY ...) window function used for?

  2. 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.

  1. QuestionThe ROW_NUMBER function
  2. QuestionThe OVER clause
  3. Click in orderNumbering rows with ROW_NUMBER
  4. Coding taskNumbering books by price
  5. Put in orderNumbering from the most expensive book
  6. QuestionThe RANK function
  7. Coding taskRanking books with RANK

Useful articles