CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
RSM
RSM

Reputation: 673

Trying to find an Oracle query to find nth highest salary and among them one with highest experience

I need to find name of the person with 3rd highest salary

The table in question is as follows:

Name       Salary    Experience
-------------------------------
Den        11000       114
Gerald     11000       148
Ellen      11000       174
Eleni      10500       149
Clara      10500       162
Janette    10000       156 
Peter      10000       150
Hermann    10000       204
Harrison   10000       169

I need to find name of the person having max experience and in the 3rd highest salary bracket.

So evidently, 3rd highest salary is 10000 and max experience among those having 3rd highest salary is Hermann with exp of 204.

I have query to find the 3rd highest salary:

select name, salary, experience 
from sal s1 
where 3 - 1 = (select count(distinct salary) 
               from sal s2 
               where s2.salary > s1.salary);

But this query returns 4 rows and I need to know how I can filter it even further in this same query to find Hermann with exp of 204.

Upvotes: 0

Views: 105

Answers (3)

Joakim Danielson
Joakim Danielson

Reputation: 51973

This query makes use of ROWNUM and MAX to find the right row. In the innermost sub-query the max experience is retreived for each salary level, ordered by salary descending, and then in the outer sub-query row numbers are added and this is joined with the original table to find the correct row(s)

SELECT s.name, s.salary, s.experience
FROM sal s
JOIN (SELECT s2.*, ROWNUM rnum
      FROM (SELECT salary, max(experience) AS m_exp
            FROM sal 
            GROUP BY salary
            ORDER BY salary DESC) s2) s3 ON s3.salary = s.salary AND 
                                            s3.m_exp = s.experience AND 
                                            rnum = 3

Upvotes: 0

MT0
MT0

Reputation: 168041

Use the DENSE_RANK analytic function to find the 3rd highest salary and the ROW_NUMBER (or RANK or DENSE_RANK) analytic function with a PARTITION BY clause to find the highest experience per salary. This only requires a single table/index scan.

Oracle Setup:

CREATE TABLE table_name ( Name, Salary, Experience ) AS
SELECT 'Den',      11000, 114 FROM DUAL UNION ALL
SELECT 'Gerald',   11000, 148 FROM DUAL UNION ALL
SELECT 'Ellen',    11000, 174 FROM DUAL UNION ALL
SELECT 'Eleni',    10500, 149 FROM DUAL UNION ALL
SELECT 'Clara',    10500, 162 FROM DUAL UNION ALL
SELECT 'Janette',  10000, 156 FROM DUAL UNION ALL
SELECT 'Peter',    10000, 150 FROM DUAL UNION ALL
SELECT 'Hermann',  10000, 204 FROM DUAL UNION ALL
SELECT 'Harrison', 10000, 169 FROM DUAL

Query: If you want to find "the person having max experience in the 3rd highest salary bracket":

SELECT Name, Salary, Experience
FROM   (
  SELECT t.*,
         DENSE_RANK() OVER ( ORDER BY Salary DESC ) AS s_rank,
         ROW_NUMBER() OVER ( PARTITION BY Salary ORDER BY Experience DESC )
           AS Exp_rownum
  FROM   table_name t
)
WHERE s_rank = 3
AND   Exp_rownum = 1;

If you swap the ROW_NUMBER() analytic function for either RANK() or DENSE_RANK() then this will return multiple people if they are tied with the joint highest experience in the 3rd highest salary bracket.

Output:

NAME    | SALARY | EXPERIENCE
:------ | -----: | ---------:
Hermann |  10000 |        204

Query: If you want to find "the person having max experience and (also) in the 3rd highest salary bracket":

Just take the query above and remove the PARTITION BY clause.

SELECT Name, Salary, Experience
FROM   (
  SELECT t.*,
         DENSE_RANK() OVER ( ORDER BY Salary DESC ) AS s_rank,
         ROW_NUMBER() OVER ( ORDER BY Experience DESC ) AS Exp_rownum
  FROM   table_name t
)
WHERE s_rank = 3
AND   Exp_rownum = 1;

Output:

(Note: if Herman's experience was 173 then this would not return any rows as Ellen would have the highest experience but she would not be in the 3rd highest salary bracket and Herman would be in the 3rd highest salary bracket but would only have the 2nd highest experience.) 

NAME    | SALARY | EXPERIENCE
:------ | -----: | ---------:
Hermann |  10000 |        204

db<>fiddle here

Upvotes: 3

BerDev
BerDev

Reputation: 1805

You don't query on the experience. So you've to add a where-clause:

select name, salary, experience 
from sal s1 
where 3 - 1 = (select count(distinct salary) 
               from sal s2 
               where s2.salary > s1.salary)
  and experience = (select max(experience) from sal)

UPDATE

The alternative (max experience within 3rd highest salary) should be:

select name, salary, experience 
from sal s1 
where 3 - 1 = (select count(distinct salary) 
               from sal s2 
               where s2.salary > s1.salary)
  and experience = (select max(experience)  from sal s3 
                    where 3 - 1 = (select count(distinct salary) 
                                   from sal s4 
                                   where s4.salary > s3.salary)
                    )

Upvotes: 0

Related Questions