Ranking functions are used to partition the records based on the needs.
There are 4 types of ranking functions available in SQL server.
Used to generate identity numbers for each rows.
Rank function will partition the record but skip the numbers.
This function will partition the record with out skipping the numbers.
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..!!!
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