Click here to Skip to main content
15,171,993 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to get the second highest salary or say nth highest salary.....pls help?
also how to get record of first n highest salaries?
Posted
Updated 15-Mar-19 22:46pm

Try this.
Select * from (select  ROW_NUMBER() OVER (ORDER BY Salary DESC) as ID, * from table) as t where ID = n

n - position of salary
   
If you can have duplicates (the same salary more than one time), one way is:
SQL
select c.*
from (select b.onetime, 
             row_number() over (order by b.onetime desc) position
      from (select distinct 
                   a.salary as onetime 
            from   sometable a) b) c
where c.position = 2


if you want yo get for example top 5 salaries then change the condition:
SQL
...where c.position <= 5
   
Here you go. Other Alternates

Second highest value[^]
   
Try
select min(Salary) from TableName where Salary in 
    (select TOP 2 (Salary) from Payroll order by Salary desc)
   
Comments
Dara Dhillon 9-Aug-11 8:27am
   
►►this one is really works the best....super like◄◄
Dara Dhillon 9-Aug-11 8:36am
   
there is limitation i found i.e if two employees have same maximum salary then the command will sort min from top 2 salaries ordered by desc which will give the result same as that of highest salary..
saj_21 28-Jan-13 4:03am
   
select min(Salary) from TableName where Salary in
(select DISTINCT TOP 2 (Salary) from Payroll order by Salary desc)
To get second highest salary,
select max(Salary) from Employee where Salary not in(select max(Salary) from Employee)
   
Comments
Dara Dhillon 9-Aug-11 8:36am
   
........ perfect one .......
SELECT *
FROM emp e
WHERE (2) = (
SELECT COUNT( DISTINCT ( e2.sal ) )
FROM emp e2
WHERE e2.sal >= e.sal
);
   
Hello friend

I will show you very simple way to find the nth Highest Salary.

Lets get started :
Step 1 : Create Table Employee

CREATE TABLE [dbo].[Employee]
(
	[Eid] [int] NULL,
	[EName] [varchar](50) NULL,
	[Salary] [money] NULL
) 

Step 2 : Insert some DummyData

Insert into Employee(Eid,EName,Salary)Values(1,'Employee1',3000)
Insert into Employee(Eid,EName,Salary)Values(2,'Employee2',5000)
Insert into Employee(Eid,EName,Salary)Values(3,'Employee3',4000)
Insert into Employee(Eid,EName,Salary)Values(4,'Employee4',1000)
Insert into Employee(Eid,EName,Salary)Values(5,'Employee5',2000)

Step 3 : Query to fetch Data

- Here we will create one Maintable and one Auxillary table and correlate the Query with the same table.

Select * from Employee E1 where 2 =
(Select count(*) from Employee E2 where E2.Salary >= E1.Salary)

Explation of Query :

1st record of Maintable(E1) i.e 3000 compared with all records from AuxillaryTable(E2)
3000>=3000 - true - count 1
5000>=3000 - true - count 2
4000>=3000 - true - count 3
1000>=3000 - false - count 3  - Count Remains Same
2000>=3000 - false - count 3  - Count Remains Same
In this case count is 3 so condition wont match, now move to next Record

2nd record of Maintable(E1) i.e 5000 compared with all records from AuxillaryTable(E2)
3000>=5000 - false - count 0
5000>=5000 - true - count 1
4000>=5000 - false - count 1  - Count Remains Same
1000>=5000 - false - count 1  - Count Remains Same
2000>=5000 - false - count 1  - Count Remains Same
In this case count is 1 so condition wont match, now move to next Record

3rd record of Maintable(E1) i.e 4000 compared with all records from AuxillaryTable(E2)
3000>=4000 - false - count 0
5000>=4000 - true - count 1
4000>=4000 - true - count 2  
1000>=4000 - false - count 2  - Count Remains Same
2000>=4000 - false - count 2  - Count Remains Same
In this case count is 2 so condition matches as we needed the 2nd highest Salary
Hence the 2nd Higest salary is 4000

If any Query feel free to ask.Thanks
   
v2
Comments
CHill60 21-Mar-19 9:19am
   
Reasons for my downvote:
1 - this is essentially the same as Solution 6 just with more words
2 - The explanation you have used implies moving to the next record - that is not how set-based processing works - there is no concept of "next record"
3 - You are listing the employees with the 2nd highest salary - OP only asked for the 2nd highest salary (i.e. you should use distinct or max)
4 - you haven't explained how to get (e.g.) the 5th highest salary
5 - this is the important one - you've used a correlated sub-query … Do you want to kill SQL Server performance? Use correlated subqueries. - Productive C# | Learn C# with Practical Video Tutorials[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900