TOP 1 WITH TIES

SQL Server 2005 ships with four functions exclusively for ranking and windowing operations. These functions will make the life of tthe T-SQL programmer easy. The following are the four functions.

1. ROW_NUMBER()

This function allows you to provide a pseudo column containing sequential integer values to result rows of a query, which makes many tasks like paging easy. Before 2005, to get the same result, you had to write complex queries like,


use pubs
GO

Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc



Lets see how easy its in 2005


CREATE TABLE #Students(StudentName VARCHAR(10), subject VARCHAR(10), Mark int)
INSERT INTO #Students VALUES('Jaison','.Net',90)
INSERT INTO #Students VALUES('Senthil','.Net',70)
INSERT INTO #Students VALUES('Joe','.Net',50)
INSERT INTO #Students VALUES('Jaison','SQL',70)
INSERT INTO #Students VALUES('Senthil','SQL',90)
INSERT INTO #Students VALUES('Joe','SQL',80)

--SELECT * FROM #Students

SELECT ROW_NUMBER() OVER (ORDER BY Mark DESC) as rownum, * FROM #Students


And here is the output.
rownum StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
2 Senthil SQL 90
3 Joe SQL 80
4 Senthil .Net 70
5 Jaison SQL 70
6 Joe .Net 50


Note that in the above example we have 2 ties, for marks 90 and 70. The ROW_NUMBER() break ties and assign a unique integer number to each row. If the column specified in the ORDER BY clause is not unique, then the ROW_NUMBER() function is not deterministic. That is the assignment of sequential values for tied rows may vary from execution to execution.

2. RANK()
The Rank function is similiar to ROW_NUMBER function in the sense that Rank() also produces a ranking column. But the difference is that the Rank function does not break ties. It assigns the same value to the tied rows. Lets see Rank() in action.


SELECT RANK() OVER (ORDER BY Mark DESC) as rank, * FROM #Students


rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
3 Joe SQL 80
3 Ann SQL 80
5 Senthil .Net 70
5 Jaison SQL 70
7 Joe .Net 50

Noe that the result has ties and gaps.

3. DENSE_RANK()
The DENSE_RANK() function is same as RANK() except that it doesnt leave gaps.


SELECT DENSE_RANK() OVER (ORDER BY Mark DESC) as rank, * FROM #Students


rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
2 Joe SQL 80
2 Ann SQL 80
3 Senthil .Net 70
3 Jaison SQL 70
4 Joe .Net 50

4.NTILE()

NTile(n) will evenly divide all the results into approximately even pieces based on the input argument, and assign each piece by the same number in the result set. For eg, if you want to assign grades to students based on their mark you can use NTILE function like


SELECT NTILE(2) OVER (ORDER BY Mark DESC) as grade, * FROM #Students


grade StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
1 Joe SQL 80
1 Ann SQL 80
2 Senthil .Net 70
2 Jaison SQL 70
2 Joe .Net 50


PARTITION BY
Ranking functions can be used for windowing with the PARTITION BY Clause. PARTITION BY clause lets you apply ranking functions for subgroups. For eg. if you want to get the ranks in the Subject Level, you canget that by using the PARTITION BY clause along with the ORDER BY Clause.


SELECT RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as rank, * FROM #Students


Here is the result.

rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
2 Senthil .Net 70
3 Joe .Net 50
1 Senthil SQL 90
2 Joe SQL 80
2 Ann SQL 80
4 Jaison SQL 70

You can apply the PARTITION BY Clause on all the ranking functions.


SELECT
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY Mark DESC) as rownum,
RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as rank,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as dese_rank,
NTILE(3) OVER (PARTITION BY subject ORDER BY Mark DESC) as grade, * FROM #Students



Here is the result.

rownum rank dese_rank grade StudentName subject Mark
-------------------- -------------------- -------------------- -------------------- ----------- ---------- -----------
1 1 1 1 Jaison .Net 90
2 2 2 2 Senthil .Net 70
3 3 3 3 Joe .Net 50
1 1 1 1 Senthil SQL 90
2 2 2 1 Joe SQL 80
3 2 2 2 Ann SQL 80
4 4 3 3 Jaison SQL 70


For more info on ranking functions, see these articles(1, 2) by SQL MVP Itzik Ben Gan.