You are here:Home » tsql » Example of Ranking/Windows Functions in SQL Server 2005

Example of Ranking/Windows Functions in SQL Server 2005

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.