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.

--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.