Search Wiki:

How to Rank Values in Groups


Jonathan Kehayias, May 11, 2008

There are several ways to pull data from SQL Server, and you might find yourself in
the position to need to pull a ranked data from a table grouped for each Key in the table.
This demonstration provides a NTILE Solution for SQL Server 2005 / SQL Server 2008 that
shows you how to pull data from the database in this manner.

Create Sample Data
-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table
CREATE TABLE Scores
(
 ScoreID int identity primary key,
 PlayerID int, 
 Score int
)
 
-- Load Sample Data
DECLARE @int1 int, @int2 int
SET @int1 = 1
 
WHILE @int1 < 100
BEGIN
	SET @int2 = 1
 
	WHILE @int2 <11
	BEGIN 
		INSERT INTO Scores SELECT @int2, @int1 * 3
		SET @int2 = @int2+1
	END
 
SET @int1 = @int1 + 1
END
 
Return to Top



In SQL 2005/2008, the NTILE operator provides simple ranking of values in ordered partitions
of equal size. If there is an uneven number of rows in the table, you may get an odd extra result
in one of the partitions.

Lowest Fifty Percent for each PlayerID

--Query to Retrieve Desired Data
 
;WITH ScoreRanking AS
(
	SELECT *, NTILE(2) OVER (PARTITION BY PlayerID ORDER BY Score) [Position]
	FROM Scores
)
 
SELECT ScoreID, PlayerID, Score
FROM ScoreRanking
WHERE Position = 1
 
Return to Top

Middle Fifty Percent for each PlayerID

--Query to Retrieve Desired Data
 
;WITH ScoreRanking AS
(
	SELECT *, NTILE(4) OVER (PARTITION BY PlayerID ORDER BY Score) [Position]
	FROM Scores
)
 
SELECT ScoreID, PlayerID, Score
FROM ScoreRanking
WHERE Position IN 2, 3
 
Return to Top

For an similar usage example, see:
SELECT TOP n by Group

To find out more about the NTILE Operator in SQL 2005/2008 see:
http://msdn.microsoft.com/en-us/library/ms175126.aspx

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, May 11, 2008
Last edited May 12 2008 at 9:22 AM  by ArnieRowland, version 1
Updating...
Page view tracker