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.
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 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 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
select Dense_Rank() over(order by Address) as 'Row Number', Name,EmpCode,Address,Salary,Dob from Employee
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 .. 🙂