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.

















1 comment:

  1. Nice example...this is a hard concept to learn, but is very useful. This also works in oracle if you take the backets out of the select and change the single quotes to double quotes like this:

    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
    /

    ReplyDelete