calculate highest nth salary using sql

Question

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

Christopher · Accepted Answer · 2017-06-24T20:08:45.563

2

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.

edited Jun 24 '17 at 20:08

answered Jun 24 '17 at 17:57

Christopher

425
3
9

So N-1 will work in a way...that will control the Inner query's count and once its got equal then from outer query record will come one by one and will be compared with the all counts. Lets say in your example, you are calculating 3rd largest salary. So inner will pick that specific two records whose salary is largest than the record coming from outer query. So this scanning is happening right...? correct me if am wrong Christopher...!! – LearnJava Jun 24 '17 at 19:27
1

yes, if the return of the subquery is equal to n-1, then the main query will display it for you as result. – Christopher Jun 24 '17 at 19:42
But this would be the first step where query starts. The moment the equal operator gets satisfied, inner query will start working. Now my question is how inner query will directly pick only those 2 records( 3rd largest salary) which are having greater salaries than the one we are looking for? How this combination happens in inner query while comparing with every record from outer query one by one..? Does oracle try every combinations( records whose salary could be greater than the one) with every record coming from outer query..? – LearnJava Jun 24 '17 at 19:48
And what it will prove..? I'm getting all records back in result.....why..? – LearnJava Jun 24 '17 at 20:10
Inner query will return 1 only when we are looking for top highest salary in records right...? – LearnJava Jun 24 '17 at 20:20
well, it is not inner query but subquery. the subquery will return value 0 if it is highest as the subquery does a count on how many employees have less salary than him. – Christopher Jun 24 '17 at 20:27

Gordon Linoff · Answer 2 · 2017-06-24T15:23:28.423

1

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.

edited Jun 24 '17 at 15:23

answered Jun 24 '17 at 15:21

Gordon Linoff

1,242,037
58
646
786

Thanks for your reply, but this solution I have already but really want to understand how its working..!! Could you please share something ..? – LearnJava Jun 24 '17 at 15:23
1

You are much better off learning the correct way to do things. You can learn about correlated subqueries -- and better yet -- lateral joins (a generalization) for the problems that really need them. – Gordon Linoff Jun 24 '17 at 15:24
1

Its not answer to my question...!! What's a problem with that if anyone wants to understand solution of history ...!! – LearnJava Jun 24 '17 at 15:36
May I know please how sorting is happening in this query...? – LearnJava Jun 24 '17 at 17:05

calculate highest nth salary using sql

2 Answers2