I have learn so many times about ranking functions but never got chance to used with them. But during development some of the stored procedures, i have get the best chance to practically use.
With ranking function , it really reduce the code complexity and also i enjoyed more while utilized them in stored procedures development. Below are some examples of various ranking functions.
Creating table to test Ranking Functions.
CREATE TABLE tbl_RankingFunctionTest
(
[Id] SMALLINT,
)
Inserting records in table created above.
INSERT INTO tbl_RankingFunctionTest VALUES(1);
INSERT INTO tbl_RankingFunctionTest VALUES(8);
INSERT INTO tbl_RankingFunctionTest VALUES(9);
INSERT INTO tbl_RankingFunctionTest VALUES(46);
INSERT INTO tbl_RankingFunctionTest VALUES(18);
INSERT INTO tbl_RankingFunctionTest VALUES(1);
INSERT INTO tbl_RankingFunctionTest VALUES(1);
INSERT INTO tbl_RankingFunctionTest VALUES(2);
INSERT INTO tbl_RankingFunctionTest VALUES(2);
Lets run script to check the result of ranking functions.
SELECT
[Id],
ROW_NUMBER() OVER (ORDER BY ID) AS 'ROW NUMBER',
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS 'ROW NUMBER WITH PARTITION',
RANK() OVER (ORDER BY ID) AS 'RANK',
DENSE_RANK() OVER (ORDER BY ID) AS 'DENSE RANK',
NTILE(2) OVER (ORDER BY ID) AS 'NTILE'
FROM tbl_RankingFunctionTest
ORDER BY [ID]
From the result by above script, In the image you can see the out for the various ranking function for ID 1 and 2 which marked.