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
Ranking functions
Selecting n-th row
Summary

Instruction

That's correct! The last function we'll introduce in this section is NTILE(X). It distributes the rows into a set number of groups, which is specified by X. For instance:

SELECT
  Name,
  Genre,
  EditorRating,
  NTILE(3) OVER(ORDER BY EditorRating DESC) AS GroupNo
FROM Game;

In the above example, NTILE(3) creates three groups based on the values in the column EditorRating. The "best" games will be put in group 1, the "average" games in group 2, and the "worst" games in group 3. See the picture below:

NTILE() example

A note about NTILE(): if the number of rows is not divisible by the number of groups, some groups will have one more element than others. Larger groups will be listed first.

Exercise

We want to divide games into four groups based on size, with the biggest games coming first. For each game, show its Name, Genre, Size, and the GroupNo it belongs to.

Stuck? Here's a hint!

You'll need to use NTILE(4) OVER (ORDER BY Size DESC).