Introduction
PARTITION BY
8. PARTITION BY MULTIPLE COLUMNS
Revision

Instruction

Great! Of course, you can partition rows by multiple columns. Take a look:

SELECT
  route_id,
  ticket.id,
  ticket.price,
  SUM(price) OVER (PARTITION BY route_id, date)
FROM ticket
JOIN journey
ON ticket.journey_id = journey.id;

We wanted to show each ticket with the sum of all tickets on the particular route on the particular date. Neither of the tables would suffice on its own, so we had to join them together to get all the columns.

Exercise

Show the id of each journey, the date on which it took place, the model of the train that was used, the max_speed of that train and the highest max_speed from all the trains that ever went on the same route on the same day.

Stuck? Here's a hint!

Use MAX(max_speed) OVER(PARTITION BY route_id, date).

Console

Code editor

Result

TableConsole