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.
3 responses to “Experiment With Nth Highest Or Lowest Salary Or Record in Sql”
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.
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!
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.