Thursday, February 19, 2015

Ranking functions in SQL

Ranking functions are one of the most discussed functions in SQL. It returns ranking value for each row in partition. We have four ranking functions in SQL Server.
  • Row_Number
  • Rank
  • Dense_Rank
  • NTILE

Let us create a table and populate it with some dummy data. It will help to us to understand these ranking function better.


--Create table statement
CREATE TABLE [dbo].[Employee](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NULL,
 [EmpCode] [varchar](10) NULL,
 [Address] [varchar](100) NULL,
 [Salary] [numeric](18, 2) NULL,
 [Dob] [datetime] NULL
) 

--Insert some sample data into employee table
INSERT INTO Employee (Name,EmpCode,Address,Salary,Dob) VALUES
('Mr. A','EC001','New Delhi',60000,'01-01-1983'),
('Mr. B','EC002','Banglore',70000,'01-02-1983'),
('Mr. C','EC003','Pune',70000,'01-03-1983'),
('Mr. D','EC004','Norway',8000000,'01-04-1983'),
('Ms. X','EC005','DC',7000000,'01-05-1983'),
('Ms. Y','EC006','Norwood',6500000,'01-06-1983')



Row_Number

Row_Number returns sequence of numbers over the partition and order them with the column specified in order by block. If there is no partition column specified then it will give a sequence of numbers with respect to order by column specified.


select Row_Number() over(order by Salary) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee

Here we don't have any partition logic. So SQL returns a sequence number from 1 to 6 order by salary.



select Row_Number() over(partition by Address order by Salary) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee



Here for the employee from Norway has sequence number 1 and 2(Highlighted in image). Rest of the employee has only sequence number 1 because they are from different different city.

Rank

Rank gives same rank for each group. The rank of a row is one plus the number of ranks that come before the row.


select Rank() over(order by Address) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee



Dense_Rank

Rank gives same rank for each group, without any gaps in the ranking.


select Dense_Rank() over(order by Address) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee



NTILE

Returns rows in an ordered partition into a specified number of groups.


select NTILE(2) over(partition by Address order by Name) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee


This is all about ranking functions in SQL. I hope this article will help you.
Happy Coding .. :)