Ranking in T-SQL

In Queries, we need to rank and number records. SQL Server gives you a few functions to rank your records.
RANK
Rank we can use to rank data in a normal way. In this ranking, if we have two 1 next to one will have rank 3. 
Name Marks Rank
John 75 1
Mark 75 1
Steve 64 3
Gates 54 4

USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)GO
--Ranking data over ScoreSELECT *, RANK() OVER (ORDER BY [Score] DESC) AS [RANK] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO

This ranks based on the column that you specify by OVER.

DENSE_RANK

Dense Rank is not missing ranks like in normal rank function. It has continuity over ranking.

NameMarksDense Rank
John751
Mark751
Steve642
Gates543


USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)GO
--Ranking data over ScoreSELECT *, DENSE_RANK() OVER (ORDER BY [Score] DESC) AS [DENSE RANK] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO

ROW NUMBER

Row Number don’t worry much about ranking it just give an incremental number to row. It’s simple.

NameMarksRow Number
John751
Mark752
Steve643
Gates544


USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)GO
--Numbering data over ScoreSELECT *, ROW_NUMBER() OVER (ORDER BY [Score] DESC) AS [ROW NUMBER] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO

NTile

NTile will separate your code into the number that you parse. If you want to group your results into 3 groups you can use NTILE(3). If you want to divide your results into 7 you can use NTILE(7).

NameMarkNTile(3)
Dedunu891
Dhananjaya851
Hasitha752
Shamil742
Sanjana653
Anuradha553


USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)GO
--NTile(3) over ScoreSELECT *, NTILE(3) OVER (ORDER BY [Score] DESC) AS [NTile(3)] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO

If you are going to group them into 4, you will have this result. It always tries to round to the lower numbers.

NameMarkNTile(4)
Dedunu891
Dhananjaya851
Hasitha752
Shamil742
Sanjana653
Anuradha554

USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)GO
--NTile(4) over ScoreSELECT *, NTILE(4) OVER (ORDER BY [Score] DESC) AS [NTile(4)] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO

Then if you are going to group odd numbered records by even or even numbered records by odd, first tile will have an extra record.

NameMarkNTile(5)
Dedunu891
Dhananjaya851
Hasitha752
Shamil743
Sanjana654
Anuradha555


USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)GO
--NTile(5) over ScoreSELECT *, NTILE(5) OVER (ORDER BY [Score] DESC) AS [NTile(5)] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO

If you are trying to group records by more than records number or same number it will act like ROW_NUMBER function. 


NameMarkNTile(>=6)
Dedunu891
Dhananjaya852
Hasitha753
Shamil744
Sanjana655
Anuradha556


USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)GO
--NTile(6) data over ScoreSELECT *, NTILE(6) OVER (ORDER BY [Score] DESC) AS [NTile(6)] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO


USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)GO
--NTile(8) over ScoreSELECT *, NTILE(8) OVER (ORDER BY [Score] DESC) AS [NTile(8)] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO

PARTITION BY

Let's move to Partition By clause. If you want to categorize your ranks into any other factor, you can use PARTITION BY clause. It lets you have separate rankings on a factor that you want.

NameSubjectMarkRank
HasithaEng751
AnuradhaEng552
DedunuMath891
DhananjayaMath852
SanjanaMath653
ShamilSci741

USE [tempdb]GO
--Creating table MarksCREATE TABLE [MARKS](
    [Name] varchar(25),
    [Subject] varchar(25),
    [Score] int)GO
--Inserting values into Mark tableINSERT INTO [MARKS]([Name], [Subject], [Score]) 
    VALUES ('Dedunu','Math',89),('Dhananjaya','Math',85),('Hasitha','Eng',75),('Shamil','Sci',74),('Sanjana','Math',65),('Anuradha','Eng',55)GO
--Partitioning rank by Suject, and ranking over ScoreSELECT *, RANK() OVER (PARTITION BY [Subject] ORDER BY [Score] DESC) AS [RANK] FROM MARKS
--Dropping tableDROP TABLE [MARKS]GO