Getting nth Highest salary Department wise from Employee table

Almost every interviewer asked question about the nth highest salary from employee table and always we got confused. Its very simple to get 3rd highest or 2nd highest salary from the employee table but when one more clause added to the query then it becomes difficult. Like if you are asked to get those employees name from the employee table for each departments who are getting 3rd highest salary. SO how will you do it? it is bit difficult to get result using Max or Top functions. You can not handle such queries using TOP or MAX clauses, if you try to use these then it becomes more difficult and hard to understand.  MS SQL Server and ORACLE  as well have a function called  DENSE_RANK ( ) which will solve your all such queries.
From MSDN you have the following syntax :
DENSE_RANK ( ) OVER ( [  ] < order_by_clause > )


Now in real queries how would you use this. Lets take an example for the same:
 Use the following query to create Employee Table
 
CREATE TABLE EMPLOYEE(EmpId INT Primary Key IDENTITY(1,1),EmpName VARCHAR(100),EmpSalary INT,DeptId INT) 


Now insert some rows into the table using the below queries

INSERT INTO  Employee1(EmpName,EmpSalary,DeptId) Values('Emp1',9500,1) 
INSERT INTO  Employee1(EmpName,EmpSalary,DeptId) Values('Emp2',8500,1) 
INSERT INTO  Employee1(EmpName,EmpSalary,DeptId) Values('Emp3',11500,2) 
........................ 
INSERT INTO  Employee1(EmpName,EmpSalary,DeptId) Values('Emp20',19500,4) 


Now from the above table we have to select all those employees from each departments who are getting third highest salary.
So try the following query

SELECT * FROM   
  (SELECT *,DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY EmpSalary DESC)
     AS Rnk         
     FROM Employee     
  ) X 
WHERE  X.Rnk=3 

Now from the above query we can find nth highest salary from the table just change the value of the rank.
So now here question is what is rank?
As per the MSDN site  we have the following explanation
"If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top employee have the same Salary value, they are both ranked one. The employee with the next highest Salary is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition."

 You can refer MSDN site for more details about the rank from the below link

Have a happy CODING.

Note: if you want to contribute your articles to this blog, please send a mail to dk61330.blogpost@blogger.com , It will be posted immediately. Please do not post JUNK /Irrelevant or any copyright contents, Thanks for your help and supports.



Regards
Dharmendra Kumar

Ph(O) +91-120-470-5539 | Ph(mob): +91-99113-08352

Comments