Post
Ask Question

Experiment With Nth Highest Or Lowest Salary Or Record In SQL

I was reading about this issue. Hence, I searched for  it on many blogs and websites. Each blog has described this for employees who have unique salaries, but what if a few employees have the same salary?

I am saying this because in an organization, sometimes a number of people have the same salary structure. Let's understand this with an example.

  1. --Create a table and fill some information on it  
  2. USE tempdb  
  3. GO  
  4. CREATE TABLE dbo.Employee (  
  5. EmpCode INT identity(1,1),  
  6. EmpName VARCHAR(100),  
  7. Salary int  
  8. )  
  9. GO  
  10. INSERT INTO dbo.Employee(EmpName,Salary)  
  11. SELECT 'Rakesh', 20000  
  12. UNION ALL  
  13. SELECT 'Raghu', 50000  
  14. UNION ALL  
  15. SELECT 'Anu', 30000  
  16. UNION ALL  
  17. SELECT 'Rama', 10000  
  18. UNION ALL  
  19. SELECT 'Manav', 60000  
  20. UNION ALL  
  21. SELECT 'Pankaj', 80000  
  22. UNION ALL  
  23. SELECT 'Vijay', 40000  
  24. UNION ALL  
  25. SELECT 'Ramesh', 55000  
  26. UNION ALL  
  27. SELECT 'Ganga', 65000  
  28. UNION ALL  
  29. SELECT 'Raju', 90000  
  30. UNION ALL  
  31. SELECT 'Vinay',90000  
  32. union all  
  33. Select 'Kapil',80000  
  34. GO  
  35. select * from dbo.Employee  
  36. GO   


Now, we will experiment on this table to extract the 2nd highest salary record. 

If you use your own query.
  1. ;WITH CTE AS (  
  2. SELECT  
  3.  EmpCode, EmpName, Salary,  
  4.  ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN  
  5. FROM dbo.Employee  
  6. )  
  7. SELECT EmpCode, EmpName, Salary  
  8. FROM CTE  
  9. WHERE RN = 2  
  10. GO  
 

It is wrong.

Even if you use the query given below again, it will give same wrong result.
  1. SELECT TOP 1 EmpCode, EmpName, Salary  
  2. FROM (SELECT TOP 2 EmpCode, EmpName, Salary  
  3.  FROM dbo.Employee  
  4.  ORDER BY Salary DESC  
  5. ) X  
  6. ORDER BY Salary ASC  
  7. GO  
Solution

For SQL Server 2005 & + :

In this case, we can use Dense_Rank.
  1. ;WITH CTE AS (  
  2. SELECT  
  3.  EmpCode, EmpName, Salary,  
  4.  Dense_Rank() OVER(ORDER BY Salary DESC) as RN  
  5. FROM dbo.Employee  
  6. )  
  7. SELECT EmpCode, EmpName, Salary  
  8. FROM CTE  
  9. WHERE RN = 2  
  10. GO  
Solution

For SQL Server 2000, to get highest 2nd salary, we will use the query given below.
  1. SELECT TOP 1 salary  
  2. FROM (  
  3. SELECT DISTINCT TOP 2 salary  
  4. FROM employee  
  5. ORDER BY salary DESC) a  
  6. ORDER BY salary  
This will give only salary value.

 

Kindly find the query to get all the records related to it.
  1. Select * from dbo.Employee where Salary=  
  2. (  
  3. SELECT TOP 1 salary  
  4. FROM (  
  5. SELECT DISTINCT TOP 2 salary  
  6. FROM employee  
  7. ORDER BY salary DESC) a  
  8. ORDER BY salary  
  9. )  

The solution even works for the unique salary table. For nth highest salary, change underline and red color value or the number with nth number.