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.