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
7. The range of OVER(PARTITION BY)
Summary

Instruction

That's right! Remember: window functions only work on the rows returned by the query. Take a look at the example below:

SELECT
  Id,
  Model,
  MaxSpeed,
  COUNT(Id) OVER (PARTITION BY MaxSpeed) AS TrainNumber
FROM Train
WHERE ProductionYear != 2012;

We've cut out the trains where ProductionYear = 2012, so the query won't show them – that's pretty obvious. But the window function would not even count them – we find that there are only two trains with MaxSpeed = 240, even though there is a third one (produced in 2012). Note that a GROUP BY clause within the WHERE clause will behave in the same way – GROUP BY will only take into account the rows which match its condition(s).

Exercise

For every train with more than 30 first class places and more than 180 second class places, show Id, Model, FirstClassPlaces, SecondClassPlaces, and the number of trains of each model (as TrainNumber) .