-1

how to find second and third highest salary in SQL?

I tried this syntax : 

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES E1 WHERE 2-1 = (SELECT COUNT(DISTINCT SALARY)
                               FROM EMPLOYEES E2
                               WHERE E1.SALARY > E2.SALARY);

but i need to get second and third ..n highest salary ?

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
Ahmad AL-Smadi
  • 1
  • 1

4 Answers4

1

try it :


with cte as 
SELECT row_number() over (partition by employeename order by salary desc  )rk ,FIRST_NAME, SALARY
FROM EMPLOYEES )
select * from cte where rk in (2,3)
nimajv
  • 423
  • 3
  • 11
1

Simple logic try to find the max salary first and then find to sub max salary from it

Select Max(SALARY)            // Third Highest
FROM EMPLOYEES 
where 
SALARY<(Select Max(SALARY)          // Second Highest Salary
From EMPLOYEES 
Where 
SALARY < (Select Max(Salary)      // Highest Salary
From Employees))
  • I think you need `MAX(salary)` in the middle sub-query to make it a valid solution ... and if you want the `N`th highest value this scales poorly as you increase `N`. – MT0 Sep 30 '19 at 14:09
  • yes, it was a bit typo mistake. I edited the answer :D –  Sep 30 '19 at 17:49
-1

I don't know how your tables are set up, but assuming SALARY is how much the FIRST_NAME makes...

WITH
    theRank AS
    (
        SELECT
            FIRST_NAME,
            SALARY

        FROM
            EMPLOYEES

        ORDER BY SALARY DESC
    )

    SELECT
        *
    FROM
        theRank
    WHERE
        rownum = 3 -- change to the top X you want

This first sorts all employees by their salary descending, then, you can select how many of the top you want.

artemis
  • 6,857
  • 11
  • 46
  • 99
  • This will not return any rows as the row with the highest salary will be tested against the `ROWNUM = 3` filter and `1 = 3` so the row will be discarded and then the second highest row will be tested and, since the previous row will be discarded will also be give a `ROWNUM` of 1 and, again, `1 = 3` is false and the row will be discarded ... repeat for all the rows and you get an empty results set. – MT0 Sep 30 '19 at 14:00
-2

you can use the order by and limit function.

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
LIMIT 3

Cheers

elliottmjohnson
  • 19
  • 2