Ranking Functions

Ranking functions are used to partition the records based on the needs.
There are 4 types of ranking functions available in SQL server.

ROW_NUMBER()
Used to generate identity numbers for each rows.
RANK()
Rank function will partition the record but skip the numbers.
DENSE_RANK()
This function will partition the record with out skipping the numbers.
NTILE()
This will equally divide the record set into number of parts based on the number which we specify.


Lets use all those things in a single query to see the different..

In the below example we can partition the records by giving any of the column
Name (Multiple columns can also be given with comma seperator), I have given the integer value 1 to get the identity rownumbers

Here We GO..!!!

USE ADVENTUREWORKS

GO

SELECT LOGINID,
TITLE,
ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY TITLE) AS ROWNUM,
RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS RANK,
DENSE_RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS DENSERANK,
NTILE(4)OVER(PARTITION BY 1 ORDER BY TITLE) AS NTILE
FROM HUMANRESOURCES.EMPLOYEE

Result window: