CODEWITHSUNDEEP
sqloracle-databasesubquerygreatest-n-per-group
Maria
Maria

Reputation: 604

Nth salary in SQL

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

Upvotes: 1

Views: 121

Answers (3)

GMB
GMB

Reputation: 222492

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.

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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.

Upvotes: 0

Esjay IT
Esjay IT

Reputation: 85

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.

Upvotes: 0

Related Questions