Sunday, 30 March 2014

Ranking Functions in SQL Server with example

There are four types of Ranking Functions in SQL. All the Ranking Functions are used with Over Clause
  • Row_Number: The row_number function gives each a row in a table a serial number. 

Consider below Table named 'EmpDetails', the table contains two columns 'EmpID' and 'EmpName'

Now we will implement Row_number to the above table:


select *, ROW_NUMBER() over (order by empid) as Rowno from dbo.EmpDetails 

The output of the query is as follows:

  • Rank: The Rank function gives rank to each rows in the result based on the over clause.

select *, Rank() over (order by empid) as Rowno from dbo.EmpDetails 


As seen above, the employees with empid as '1' has been given Rank 1, whereas the employee with empid '2' has rank 3, because there were two employees with empid 1 that has rank one.

  • Dense_Rank: This function is similar to Rank() function, the only difference is that it gives Rank without any gaps.

select *, dense_Rank() over (order by empid) as Rowno from dbo.EmpDetails 


If you observe the above result set, dense_rank() function has assigned rank 2 to the employees with Empid 2, unlike Rank() function which ranked them as 3.

  • Ntile: The Ntile function requires to specify a number in the parentheses and based on that the it divides the rows in the result set.

select *, Ntile(2) over (order by empid) as Rowno from dbo.EmpDetails 

Here, we have specified 2 in the parentheses, hence the result set with contain only two ranks i.e. 1 and 2



1 comment:

  1. Tableau online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Tableau online Training, Tableau Training, Tableau, Tableau online Training| Tableau Training| Tableau| "Courses at 21st Century Software Solutions
    Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 -