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.
- --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 the 2nd highest salary record.
If you 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 use the query given below 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 can 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 the query given below.
- 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.
Kindly find the query to get all the 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 works for the unique salary table. For nth highest salary, change underline and red color value or the number with nth number.