See more: SQL2005
Hi to all,
how can i find 2nd,3rd highest salary in sql server
Posted 30 Jul '12 - 0:27
panduu453

## Solution 12

For second highest salary

Select * from TableName as A where (1)=select count(distinct(B.salary) from TableName as B where A.salary< B.Salary)

if You Want any n-1 highest value u can easily use this code

Select * from TableName as A where (n-1)=select count(distinct(B.salary) from TableName as B where A.salary< B.Salary)
Member 9672594 - 8 Feb '13 - 1:08
How to give value "n" in where condition can u give clear explanation

## Solution 5

Here is a great link with a very easily understood solution:

Find nth highest salary in SQL
Maciej Los - 9 Jan '13 - 15:27
Good link, but not good enough. ;)

## Solution 6

Hi Pandu,

Try this code block....

SELECT Salary,EmpName FROM
( SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
FROM EMPLOYEE ) As A
WHERE A.RowNum IN (2,3)

Thank you ..
Maciej Los - 9 Jan '13 - 15:35
Good, but not good enough!

## Solution 3

Please use RANK method as well as ROW_NUMBER method is TSQL.

http://msdn.microsoft.com/en-us/library/ms189798.aspx[^]
Maciej Los - 9 Jan '13 - 15:30
Good link, but ROW_NUMBER() will not help ;(

## Solution 4

Maciej Los - 9 Jan '13 - 15:27
Good links, but not good enough. ;)

## Solution 1

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Here replace n with the numbers you wanted..

For eg if you want 2nd highest salary then

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

if you want 3rd highest salary then

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

if you want 4th highest salary then
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 4 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Neetesh Agarwal - 10 Oct '12 - 0:01
Hello Santosh, I also need name of the employee..........
Gireesh9babu - 6 Nov '12 - 2:32
hi Santhosh kumar.. if there is any duplicate on salary field like salary ______ 1000 1000 1500 1000 2000 using with above query 1st max salary-- the result will be 2000 that's fine in 2nd max salary 1500 that's fine in 3rd max salary it will display 1000 but it is not displaying all the 3rd max salaries.. so please tell me how to find with duplicate
Santhosh Kumar J - 6 Nov '12 - 2:37
Why do you want to find duplicate. If you want it with duplicates, then the above query wont work for you, because am taking out distinct salaries
talibmukadam - 28 Nov '12 - 5:37
In case of duplicated value like Person Salary A 40000 B 30000 C 30000 D 35000 E 35000 F 20000 G 10000 H 40000 I 25000 Use the below query SELECT * FROM salary WHERE salary = ( SELECT salary FROM details GROUP BY salary ORDER BY salary DESC LIMIT N-1,1) For the 4th highest salary, give value of n as 4 and the output will be B 30000 C 30000 Hope This might help
Maciej Los - 9 Jan '13 - 15:32
See my solution.
Maciej Los - 9 Jan '13 - 15:32
Good answer (4), because of no details in question. See my solution.

## Solution 2

select top 3 P.*   from Table1  P
,(select  distinct top 3  ID,balamt from Table1 order by balamt desc) A  where P.ID=A.ID
Member 9530686 - 8 Nov '12 - 2:30
this is very helpful for me thank you very mutch code project

## Solution 8

In case of duplicated value like

Person Salary

A 40000
B 30000
C 30000
D 35000
E 35000
F 20000
G 10000
H 40000
I 25000

Use the below query

SELECT * FROM salary WHERE salary = ( SELECT salary FROM details GROUP BY salary ORDER BY salary DESC LIMIT N-1,1)

For the 4th highest salary, give value of n as 4 and the output will be

B 30000
C 30000

Hope This might help

## Solution 11

My table structure is like this..

tablename is A.

ID Salary
1 5000
2 15000
3 12000
4 11000
5 6000
6 8000
7 8880
8 9000

select * from
(select ROW_NUMBER() OVER(ORDER BY Salary desc) SrNo, Salary From A) tbl
where tbl.SrNo = 2

using the ROW_NUMBER() concept, we can easily find 2nd or 3rd highest salary.

## Solution 13

You need to use one of the ranking functions[^].

DECLARE @tEmp TABLE (eName NVARCHAR(30), eSalary INT)

INSERT INTO @tEmp (eName, eSalary)
VALUES('A', 40000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('B', 30000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('C', 30000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('D', 35000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('E', 35000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('F', 20000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('G', 10000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('H', 40000)
INSERT INTO @tEmp (eName, eSalary)
VALUES('I', 25000)

SELECT eName, eSalary, RANK() OVER(ORDER BY eSalary DESC) AS [Rank]
FROM @tEmp
the output recordset is:
eName eSalary Rank
A     40000   1
H     40000   1
D     35000   3
E     35000   3
B     30000   5
C     30000   5
I     25000   7
F     20000   8
G     10000   9

As you see, the 2., 3. and 4. salary doesn't exists, because of the same values for (A,H), (D,E) and (B,C)

So, the query:
SELECT *
FROM (
SELECT eName, eSalary, RANK() OVER(ORDER BY eSalary DESC) AS [Rank]
FROM @tEmp
) AS t1
WHERE t1.[Rank] IN (2,3,4)
will produce recordset:
D	35000	3
E	35000	3

To correct it, you need to replace RANK() with NTILE(4), as is shown below:
SELECT *
FROM (
SELECT eName, eSalary, NTILE(4) OVER(ORDER BY eSalary DESC) AS [Rank]
FROM @tEmp
) AS t1
WHERE t1.[Rank] IN (2,3,4)

Result:
E	35000	2
B	30000	2
C	30000	3
I	25000	3
F	20000	4
G	10000	4

Is this what you are looking for?