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

Window functions and GROUP BY
Summary and Review

Instruction

Thanks to how window functions behave with GROUP BY, it's fairly simple to create quite advanced statistic.

Let's take a look at other use cases.

For instance, we may create a ranking based on an aggregate function. Take a look:

SELECT
  Country,
  COUNT(Id),
  RANK() OVER(ORDER BY COUNT(Id) DESC)
FROM Auction
GROUP BY Country;

This query groups auctions by country, counts the number of auctions from each country and then creates a ranking based on the number of auctions.

Exercise

Group the auctions based on category. Show the CategoryId, the sum of final prices for all auctions in this category, and a ranking based on that sum. Have the highest sum come first. Name the last two columns SumFinalPrice and Ranking.

Stuck? Here's a hint!

Use

RANK() OVER(ORDER BY SUM(FinalPrice) DESC)

as your window function.