Introduction
PARTITION BY
8. Using PARTITION BY with multiple columns
Summary

Instruction

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

SELECT
  RouteId,
  Ticket.Id,
  Ticket.Price,
  SUM(Ticket.Price) OVER(PARTITION BY Journey.RouteId, Journey.Date) AS SumPrice
FROM Ticket
INNER JOIN Journey
  ON Ticket.JourneyId = Journey.Id;

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

Exercise

Show the Id of each journey, the Date on which it took place, the Model of the train that was used, the MaxSpeed of that train and the highest MaxSpeed of any train travelling the same route on the same day (HighestMaxSpeed).

Stuck? Here's a hint!

Use MAX(MaxSpeed) OVER(PARTITION BY RouteId, Date).