15,171,993 members
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

## Solution 1

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

## Solution 2

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`

## Solution 3

Here you go. Other Alternates

Second highest value[^]

## Solution 4

Try
```select min(Salary) from TableName where Salary in
(select TOP 2 (Salary) from Payroll order by Salary desc)```
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)

## Solution 5

To get second highest salary,
`select max(Salary) from Employee where Salary not in(select max(Salary) from Employee)`
Dara Dhillon 9-Aug-11 8:36am

........ perfect one .......

## Solution 6

SELECT *
FROM emp e
WHERE (2) = (
SELECT COUNT( DISTINCT ( e2.sal ) )
FROM emp e2
WHERE e2.sal >= e.sal
);

## Solution 9

```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
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[^]