Find 2nd,3rd highest salary in sql server

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)

Permalink

Posted 9 Jan '13 - 8:43

vivek shaushi597

Edited 9 Jan '13 - 8:50

Abhishek Pant14.9K

Comments

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

Permalink

Posted 9 Oct '12 - 21:08

Joeporter377

Comments

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 ..

Permalink

Posted 9 Oct '12 - 21:27

damodara naidu betha2.7K

Edited 9 Jan '13 - 8:49

Abhishek Pant14.9K

Comments

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

Permalink

Posted 30 Jul '12 - 19:01

Sangunni3.3K

Comments

Maciej Los - 9 Jan '13 - 15:30

Good link, but ROW_NUMBER() will not help ;(

Solution 4

http://csharpdotnetfreak.blogspot.com/2011/09/select-nth-highest-record-sql-server.html[^]

http://blog.sqlauthority.com/2008/04/02/sql-server-find-nth-highest-salary-of-employee-query-to-retrieve-the-nth-maximum-value/[^]

Permalink

Posted 30 Jul '12 - 19:02

Sangunni3.3K

Comments

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

Permalink

Posted 30 Jul '12 - 0:44

Santhosh Kumar Jayaraman23.2K

Comments

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

Permalink

Posted 30 Jul '12 - 1:44

Vasim8892.1K

Comments

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

Permalink

Posted 27 Nov '12 - 23:43

talibmukadam266

Edited 9 Jan '13 - 8:46

Abhishek Pant14.9K

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.

Permalink

Posted 25 Dec '12 - 6:32

trushant pachnekar1.5K

Edited 9 Jan '13 - 8:47

Abhishek Pant14.9K

Solution 13

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

Using your example:

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:

Is this what you are looking for?

Permalink

Posted 9 Jan '13 - 9:22

Maciej Los54.8K

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

0	OriginalGriff	296
1	Mohammed Hameed	183
2	Sergey Alexandrovich Kryukov	143
3	Santhosh G_	108
4	Mayur_Panchal	98

0	Sergey Alexandrovich Kryukov	8,216
1	OriginalGriff	6,271
2	CPallini	3,528
3	Rohan Leuva	2,703
4	Maciej Los	2,234

Find 2nd,3rd highest salary in sql server

10 solutions

Solution 12

Solution 5

Solution 6

Solution 3

Solution 4

Solution 1

Solution 2

Solution 8

Solution 11

Solution 13

Add your solution here

Preview 0

		9,879,055 members (32,678 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using

Find 2nd,3rd highest salary in sql server

10 solutions

Solution 12

Solution 5

Solution 6

Solution 3

Solution 4

Solution 1

Solution 2

Solution 8

Solution 11

Solution 13

Add your solution here

Preview 0

Existing Members

...or Join us