Nth salary in SQL

Question

I'm trying to understand below query, how its working.

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )

Lets say I have 5 distinct salaries and want to get 3rd largest salary. So Inner query will run first and then outer query ?

I'm getting confused how its being done in sql engine. Curious to know. Becasue if its 3rd largest then 3-1 = 2, so that 2 needs to be matched with inner count as well. How inner count is being operated.

Can anyone explain the how its working .. ?

`DISTINCT` is not a function. Skip those redundant parentheses to write clearer code, i.e. simply do `SELECT COUNT(DISTINCT Emp2.Salary)`. — jarlh, Feb 20 '20 at 12:15
I'd use a subquery to return the 3rd largest distinct salary. — jarlh, Feb 20 '20 at 12:26

GMB · Answer 1 · 2020-02-20T13:50:59.620

1

The subquery is correlated subquery, so it conceptually executes once for each row in the outer query (database optimizations left apart).. What it does is count how many employees have a salary greater than the one on the row in the outer query: if there are 2 employee with a higher salary, then you know that the employee on the current row in the outer query has the third highest salary.

Another way to phrase this is to use row_number() for this:

select *
from (
    select 
        e.*, 
        row_number() over(order by salary desc) rn 
    from employee e
) t
where rn = 3

Depending on how you want to handle duplicates, dense_rank() might also be an option.

edited Feb 20 '20 at 13:50

answered Feb 20 '20 at 10:50

GMB

216,147
25
84
135

So is it correct to say that, In this scenario outer query is running first and then inner query ..? Ideally inner query runs first. – Maria Feb 20 '20 at 11:01
1

@Maria: indeed. The subquery references a column that comes from the outer query, so it cannot possibly execute first. – GMB Feb 20 '20 at 11:04
I would probably use `dense_rank()` instead of `row_number()` – Feb 20 '20 at 13:46
@a_horse_with_no_name: OP did not really tell how they want to handle duplicates, so I went for `row_number()`. I edited my answer to mention `dense_rank()` too. – GMB Feb 20 '20 at 13:51

score 0 · Answer 2 · answered Feb 20 '20 at 12:52

0

SELECT * FROM (SELECT EMP.ID,RANK() OVER (ORDER BY SALARY DESC) AS NOS FROM EMPLOYEE) T WHERE T.NOS=3

Then from this select the one with any desired rank.

answered Feb 20 '20 at 12:52

Esjay IT

85
5

score 0 · Answer 3 · answered Feb 20 '20 at 13:40

It is easier to understand when you run this query:

select e1.*, 
       (select count(distinct e2.salary)
          from employee e2
          where e2.salary > e1.salary) as n
  from employee e1

This is my sample table:

create table employee(salary) as (
  select * from table(sys.odcinumberlist(1500, 1200, 1400, 1500, 1100)));

so my output is:

    SALARY          N
---------- ----------
      1500          0
      1200          2
      1400          1
      1500          0
      1100          3

As you can see that subquery counts, for each row, salaries which are greater than salary in current row. So for instance, for 1400 there is one DISTINCT greater salary (1500). 1500 appears twice in my table, but distinct makes that it is counted once. So 1400 is second in order.

Your query has this count moved to the where part and compared with required value. We have to substract one, because for highest salary there is no higher value, for second salary one row etc.

It's one of the methods used to find such values, newer Oracle versions introduced analytic functions (rank, row_number, dense_rank) which eliminates the need of using subqueries for such purposes. They are faster, more efficient. For your query dense_rank() would be useful.

Nth salary in SQL

3 Answers3