How do i use the query listed below to retrieve the third highest earners' name and salary?

Question

The following example displays the top three earners’ names and salaries. These employees belong to department 1002 in the EMPLOYEE table:

SELECT ROWNUM, fname, salary
FROM (SELECT fname, salary
FROM employee
WHERE dept_id=1002
ORDER BY salary DESC)
WHERE ROWNUM <=3;

In the above example, the inline view retrieves the first name and salary of employees working in department 1002 in descending order of their salary. The WHERE condition of the outer query specifies that the result set would return only the first three rows.

And the question is? Or your problem? – Plouf Jan 28 '13 at 11:12 — Plouf, Jan 28 '13 at 11:12
I need only the third highest earner's name and salary. – HOLYBIBLETHE Jan 28 '13 at 13:52 — HOLYBIBLETHE, Jan 28 '13 at 13:52

score 3 · Accepted Answer · edited May 23 '17 at 11:53

This is pretty much the same as your previous question, or at least the answer is similar to the one you got for that:

select fname, salary
from (
    select fname, salary, 
        dense_rank() over (order by salary desc) as rn
    from employee
    where dept_id=1002
)
where rn = 3;

dense_rank() is an analytic function that gives you a gapless ranking. The rownum pseudocolumn or the 'superior' row_number() analytic function, and rank(), do similar things but treat ties differently.

With rownum/row_number(), two records with the same salary will be returned in an arbitrary order, with different 'ranks', i.e. you'll always have records ranked 1, 2, 3, 4, 5, 6 even with ties.

With rank(), two records with the same salary will be returned with the same rank value, and there will be a gap in the rank values to the next salary value. So you might get ranks of 1, 1, 1, 4, 4, 6 - with all three ranked '1' having the same salary, and both records ranked '4' having the same salary. The ordering is still arbitrary within records assigned the same rank.

With dense_rank(), two records with the same salary will be returned with the same rank value, and there will not be a gap in the 'rank' values to the next salary value. So you might get ranks of 1, 1, 1, 2, 2, 3 - with all three ranked '1' having the same salary, and both records ranked '2' having the same salary. The ordering is still arbitrary within records assigned the same rank.

With some sample data in a CTE:

with t as (
    select 20000 as salary from dual
    union all select 30000 from dual
    union all select 40000 from dual
    union all select 30000 from dual
    union all select 40000 from dual
    union all select 40000 from dual
)
select salary,
    row_number() over (order by salary desc) as row_num,
    rank() over (order by salary desc) as r,
    dense_rank() over (order by salary desc) as dr
from t;

    SALARY    ROW_NUM          R         DR
---------- ---------- ---------- ----------
     40000          1          1          1
     40000          2          1          1
     40000          3          1          1
     30000          4          4          2
     30000          5          4          2
     20000          6          6          3

6 rows selected.

With rank() you might not have anything that's ranked as 3; with dense_rank() you might have more than one with that rank. row_number() will have exactly one, but if there are ties you won't know if it's actually the third highest. You'll need to decide which matches what you're trying to do.

score 1 · Answer 2 · answered Jan 28 '13 at 14:27

1

Use a rank() over (order by salary) in the inner query, and in the outer query just pick rank=3.

answered Jan 28 '13 at 14:27

Plouf

627
3
7

How do i use the query listed below to retrieve the third highest earners' name and salary?

2 Answers2