CODEWITHSUNDEEP
sqloracle-databasetop-n
LearnJava
LearnJava

Reputation: 382

calculate highest nth salary using sql

I want to calculate highest nth salary in Oracle. I've already done with my solution but on google I found one query doing the same thing.

Here is the query

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

Data

   ID   Name    Birth       Orig_Salary
    2   John    15-JUL-97   2341    
    3   Joe     25-JAN-86   4321    
    4   Tom     13-SEP-06   2413    
    5   Jane    17-APR-05   7654    
    6   James   18-JUL-04   5679    
    7   Jodd    20-JUL-03   5438    
    8   Joke    01-JAN-02   8765    
    9   Jack    29-AUG-01   7896

I'm not not able to understand this query. After running inner query it will always gives me count of 8 after doing this it will go to where clause where it will pick salary which is higher than the outer query salary. How equal operator is working here in between inner and outer query and how comparison is happening.

Could any help me to understand how this query is working technically in back-end..?

Upvotes: 1

Views: 194

Answers (2)

Christopher
Christopher

Reputation: 435

SELECT *
FROM Employee Emp1
WHERE (N - 1) = (SELECT COUNT(DISTINCT(Emp2.orig_salary))
                FROM Employee Emp2  <--- cartesian product with same table
                WHERE emp2.orig_salary > emp1.orig_salary)  <---- but  do the cartesian product only if line of salary of emp 2 is greater than the current line of Emp1 'salary

e.g assume there are only 3 lines in the table:

ID Name Birth Orig_Salary

2   John    15-JUL-97   2341    
3   Joe     25-JAN-86   4321    
4   Tom     13-SEP-06   5413

the main query will look at the first line --> 2 John 15-JUL-97 2341 <---, and subquery will return 2 because the salaries 4321 (emp2.orig_salary) and 5413 (emp2.orig_salary) are greater than 2341 (emp1.orig_salary)

the main query will then look at the second line --> 3 Joe 25-JAN-86 4321 <---, and subquery will return 1 because the salaries 5413 (emp2.orig_salary) is greater than 2341 (emp1.orig_salary)

when i say subquery, it is the 

=(SELECT COUNT(DISTINCT(Emp2.orig_salary))
                 FROM Employee Emp2  <--- cartesian product with same table
                WHERE emp2.orig_salary > emp1.orig_salary)

and the main query is 

SELECT *
FROM Employee Emp1
WHERE

the returned value from the subquery is then compare to the where condition n-1, if the condition is satisfied, then it retrieves the line.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

There is no need to understand that query. The correct formulation is:

SELECT Emp1.*
FROM (SELECT Emp1.*, DENSE_RANK() OVER (ORDER BY Emp2.orig_salary) as seqnum
      FROM Employee Emp1
     ) Emp1
WHERE seqnum = <n>;

This gives the details for the employees. If you just want the salary:

SELECT orig_salary
FROM (SELECT Emp1.*, DENSE_RANK() OVER (ORDER BY Emp2.orig_salary) as seqnum
      FROM Employee Emp1
     ) Emp1
WHERE seqnum = <n> AND rownum = 1;

I should note that a simpler version of this is:

select distinct orig_salary
from employees
order by orig_salary desc
offset <n - 1>
fetch first 1 row only;

The use of a correlated subquery for this is a pleasant anachronism from the days when relational databases were not as powerful as they are now. It is of historical interest.

Upvotes: 1

Related Questions