Experiment With Nth Highest Or Lowest Salary Or Record in Sql


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

EmployeeDummySalary
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
salarywrong1
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
rightans1


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-
rightans2 
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
)
rightans1
The solution even work for unique salary table.
For nth highest Salary change bold & blue color value or number with nth number. 
,

3 responses to “Experiment With Nth Highest Or Lowest Salary Or Record in Sql”

  1. Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

  2. Hello there! This is my 1st comment here so I just wanetd to give a quick shout out and say I really enjoy reading your articles. Can you suggest any other blogs/websites/forums that cover the same topics? Appreciate it!

  3. Excellent read, I just passed this onto a cloelague who was doing a little research on that. And he actually bought me lunch as I found it for him smile So let me rephrase that: Thank you for lunch! Bill Dickey is learning me his experience. by Lawrence Peter Berra.