I was reading about it so i searched it on so many blogs & websites. Each blog has described this for employees having unique salaries, but what if few employees have same salary.
I am asking this because in an organization few people have same salary structure. Lets understand this with an example-
--Create a table and fill some information on it USE tempdb GO CREATE TABLE dbo.Employee ( EmpCode INT identity(1,1), EmpName VARCHAR(100), Salary int ) GO INSERT INTO dbo.Employee(EmpName,Salary) SELECT 'Rakesh', 20000 UNION ALL SELECT 'Raghu', 50000 UNION ALL SELECT 'Anu', 30000 UNION ALL SELECT 'Rama', 10000 UNION ALL SELECT 'Manav', 60000 UNION ALL SELECT 'Pankaj', 80000 UNION ALL SELECT 'Vijay', 40000 UNION ALL SELECT 'Ramesh', 55000 UNION ALL SELECT 'Ganga', 65000 UNION ALL SELECT 'Raju', 90000 UNION ALL SELECT 'Vinay',90000 union all Select 'Kapil',80000 GO select * from dbo.Employee GO Now we will experiment on this table to extract 2nd highest salary record : If you will use your own query-- ;WITH CTE AS ( SELECT EmpCode, EmpName, Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN FROM dbo.Employee ) SELECT EmpCode, EmpName, Salary FROM CTE WHERE RN = 2 GO It is wrong. even if you will use below query again it will give same wrong result: SELECT TOP 1 EmpCode, EmpName, Salary FROM (SELECT TOP 2 EmpCode, EmpName, Salary FROM dbo.Employee ORDER BY Salary DESC ) X ORDER BY Salary ASC GO
Solution : For SQL Server 2005 & + : In this case we should use Dense_Rank. ;WITH CTE AS ( SELECT EmpCode, EmpName, Salary, Dense_Rank() OVER(ORDER BY Salary DESC) as RN FROM dbo.Employee ) SELECT EmpCode, EmpName, Salary FROM CTE WHERE RN = 2 GO
Solution : For SQL Server 2000 : To get highest 2nd salary we will use below query- SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary This will give only salary value- To get all records related to it- Select * from dbo.Employee where Salary= ( SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary )
The solution even work for unique salary table. For nth highest Salary change bold & blue color value or number with nth number.