3rd highest salary sql

Question

I try to get all 3 highest salary from top 5 employees like this

query

select top 5
  (SELECT MAX(grosssalary) FROM Detail) maxsalary ,
   (SELECT MAX(grosssalary) FROM Detail) sec_max_salary,
  (SELECT MAX(grosssalary) FROM Detail
  WHERE grosssalary NOT IN (SELECT MAX(grosssalary) FROM Detail )) as third_max_salary

but this shows data like this

maxsalary   sec_max_salary  third_max_salary
550000       550000            41000

where as i want data like this

   maxsalary    sec_max_salary  third_max_salary
    550000       90000            41000

Possible duplicate : http://stackoverflow.com/questions/16234983/how-to-find-third-or-nth-maximum-salary-from-salary-table — thebenman, Dec 05 '16 at 05:58

Jibin Balachandran · Accepted Answer · 2016-12-05T06:06:01.390

2

Do a CTE and get the ROWNUMBER() on salary DESC and in outer query fetch the record with rownumber equal to 3.

;WITH CTE AS
(
    SELECT RN = ROW_NUMBER() OVER (ORDER BY salary DESC),
           Salary
     FROM [YourTable]
 )

 SELECT Salary
 FROM CTE
 WHERE RN <= 3

Note: If you want 3rd highest salary use RN=3 if you want all top 3 salary then use RN<=3

If you want top 3 highest salary then you can do this as well:

SELECT TOP 3 Salary
FROM [YourTable]
ORDER BY Salary DESC

edited Dec 05 '16 at 06:06

answered Dec 05 '16 at 06:00

Jibin Balachandran

3,381
1
24
38

what is CTE? and this is complicated query please modify according t my query – super user Dec 05 '16 at 06:07
1

@superuser this will give you the top 3 salaries but they could still all be the same salary. If you are looking for the top 3 different salaries then all you would need to do is switch ROW_NUMBER() to DENSE_RANK(). Also note using PIVOT with this out put will get you to 3 columns instead of rows nicely. – Matt Dec 05 '16 at 07:29

score 1 · Answer 2 · answered Dec 05 '16 at 06:08

 CREATE TABLE #A
 (

 salary INT
 )
 INSERT INTO #A VALUES
  (35000),
  (34000),
  (20000),
  (12000),
  (500  ),
  (40000),
  (25000),
  (41000),
  (90000),
  (550000)


select [1] maxsalary, [2] sec_max_salary, [3] third_max_salary
from (
SELECT *
FROM (

    SELECT *,
           RN = ROW_NUMBER() OVER (ORDER BY Salary DESC)
    FROM #A
)a
WHERE RN  <= 3

) src
pivot
(
  MAX(SALARY)
  for RN in ([1], [2], [3])
) piv;

OUTPUT

maxsalary   sec_max_salary  third_max_salary
550000       90000           41000

Naveen · Answer 3 · 2018-07-27T12:43:01.307

Table Name:-ThreeHighestSalaries

name -------- Salary

e5 -------- 45000
e3 -------- 30000
e2 -------- 49000
e4 -------- 36600
e1 -------- 58000
e6 -------- 58000

Should return 58000,49000,45000.

1)select *,dense_rank() over(order by salary desc) as salaryrank from ThreeHighestSalaries

The above query returns:

Name --------- Salary------Rank

e1 --------- 58000 --------- 1
e6 --------- 58000 --------- 1
e2 --------- 49000 --------- 2
e5 --------- 45000 --------- 3
e4 --------- 36600 --------- 4
e3 --------- 30000 --------- 5

2)select distinct salary from (select *,dense_rank() over(order by salary desc) as salaryrank from ThreeHighestSalaries) as temp where temp.salaryrank<=3 order by salary desc

The above query returns result as:

Salary

58000
49000
45000

Ullas · Answer 4 · 2016-12-05T07:44:10.787

0

Use DENSE_RANK() to order the salary and then use a CASE expression to find the top 3 salaries.

Query

;with cte as(
    select [sal_rank] = dense_rank() over(
        order by [grosssalary] desc
    ), *
    from [your_table_name]
)
select max(case [sal_rank] when 1 then [grosssalary] end) as [maxsalary],
max(case [sal_rank] when 2 then [grosssalary] end) as [sec_max_salary],
max(case [sal_rank] when 3 then [grosssalary] end) as [third_max_salary]
from cte;

edited Dec 05 '16 at 07:44

answered Dec 05 '16 at 06:07

Ullas

11,450
4
33
50

@superuser : [Common Table Expression](https://msdn.microsoft.com/en-IN/library/ms175972.aspx) – Ullas Dec 05 '16 at 06:20
RANK() here could be problematic because if 2 people share the top salary it would rank 1 1 3 so you second max salary would be NULL. If you want to allow for ties like this DENSE_RANK() would work better but the conditional aggregation is a good tip for the OP – Matt Dec 05 '16 at 07:24
@Matt : Yeah that's correct.. `DENSE_RANK` will be fine. – Ullas Dec 05 '16 at 07:43

score 0 · Answer 5 · answered Dec 05 '16 at 06:22

If you are using SQL SERVER 2012+ then use OFFSET

SELECT TOP 1 salary,
             (SELECT salary FROM Yourtable
              ORDER  BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY),
             (SELECT salary FROM Yourtable 
              ORDER  BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)
FROM   Yourtable
ORDER  BY salary DESC

If you want just the 3rd highest salary then

SELECT salary
FROM   yourtable
ORDER  BY salary DESC 
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

score 0 · Answer 6 · answered Aug 02 '17 at 10:31

Below code will Lead you to get N'th largest or Smallest salary values irrespective to Datatype ie. integer or Vearchar. just change the '<' sign for smaller n'th values.

select * from txt_salary as t1 where 'nth largest' =
(select count(salary) from txt_salary as t2 where t1.salary<=t2.salary)

score 0 · Answer 7 · answered Nov 24 '17 at 07:33

For SQL SERVER

SELECT e1.* FROM emp e1 WHERE e1.salary = ( select MIN(e2.salary) from (select top 3 DISTINCT(e3.salary) from emp as e3 ORDER by e3.salary desc ) as e2 )

For MYSQL

SELECT e1.* FROM emp e1 WHERE e1.salary = ( select MIN(e2.salary) from (select DISTINCT(e3.salary) from emp as e3 ORDER by e3.salary desc limit 0,3) as e2 )

score 0 · Answer 8 · answered Mar 01 '18 at 07:04

select max(salary) as highest from employee
UNION
select max(salary) as 2nd_highest from employee where salary<(select max(salary) from employee)
UNION
max(salary) as 3rd_highest from employee where salary<(select max(salary) from employee where salary< (select max(salary) from employee))=

For 18th highest salary=
select salary, ename, eid FROM (select e.ename, e.salary, e.eid, rank() OVER (order by e.eid desc) rank) WHERE rank=18

score 0 · Answer 9 · answered Sep 10 '18 at 05:18

0

declare @tem1 table(salary int)

insert into @tem1(salary) select top 3 grosssalary from Detail order by grosssalary desc

select top 1 sal as 3rdsalary from @tem1 order by sal asc

answered Sep 10 '18 at 05:18

Ishara Samintha

460
6
8

score -2 · Answer 10 · edited Aug 29 '17 at 08:35

-2

select * 
from 
( select *
  from table 
  order by salary desc
  limit 3 
) as top_three
order by top_three.salary asc
limit 1

edited Aug 29 '17 at 08:35

Karl Gjertsen

4,690
8
41
64

answered Dec 05 '16 at 06:38

santosh

118
6

1

sql-server uses SELECT TOP not limit – Matt Dec 05 '16 at 07:25

3rd highest salary sql

10 Answers10

name -------- Salary

Name --------- Salary------Rank

Salary