Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

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 traveling the same route on the same day (HighestMaxSpeed).

Stuck? Here's a hint!

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