Ranking Functions #1

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used.Ranking functions are nondeterministic.*

The four ranking functions are ROW_NUMBER(), NTILE(), RANK() and DENSE_RANK() in SQL.

#1 ROW_NUMBER() function

“Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.”
Syntax

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )

[ partition_by_clause ] is optional

partition_by_clause
Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied.
order_by_clause
Determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Example

--********** RANKING FUNCTION #1 ROW_NUMBER() ********
-- Create table
CREATE TABLE RankTest (CandidateID int NOT NULL,
[Year] smallint NOT NULL,
Department VARCHAR(100) NOT NULL
);

-- Insert data
INSERT INTO RankTest (CandidateID, [Year], Department)
select 1,2010,'SOFTWARE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'ECOMMERCE' UNION ALL
select 1,2010,'BIZINFO' UNION ALL select 1,2010,'SOFTWARE' UNION ALL
select 2,2010,'SOFTWARE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'ECOMMERCE' UNION ALL
select 2,2010,'BIZINFO' UNION ALL select 2,2010,'SOFTWARE';

--ROW_NUMBER() #1
SELECT CandidateID ,[Year] , Department , ROW_NUMBER() OVER (PARTITION BY Department ORDER BY CandidateID)
FROM RankTest

--ROW_NUMBER() #2
SELECT CandidateID ,[Year] , Department , ROW_NUMBER() OVER (ORDER BY Department)
FROM RankTest