Explanation of the query for getting the 3 maximum salaries

Question

Can any one explain the below query for getting the 3 maximum salaries?

select distinct sal
  from emp a
where 3 >= (select count(distinct sal)
              from emp b
            where a.sal <= b.sal)
order by a.sal desc;

Someone suuggested to me the use of the above query to get 3 max. salaries in a table. I didn't understand what is happening in the below part of the query:

3>= (select count(distinct sal)
       from emp b
     where a.sal <= b.sal) ;

Can anyone explain it? if there is any other way to get the same result,please advice me with query

score 8 · Accepted Answer · edited Jul 10 '19 at 08:39

   empid    sal
   ===============
    1       300
    2        50
    3       400
    4       200
    5       150
   ================

select distinct sal from emp a where 3        --outer query
  >=
 (select count(distinct sal) from emp b             --inner query(correlated)
        where a.sal <= b.sal) order by a.sal desc;

This query fetches all the records from the outer query i.e emp a and iterates them one by one, passing value to the inner query.

Let's take an example:

It fetches 1st row, which is 1, 300 and passes this value to the inner query
The inner query tries to find a distinct sal value that is less than or equal to the records in emp table b
The count is 3, because 50, 200, 150 are less than 300. Since 3 >= 3 (inner query result) the answer is true and 300 is selected.
Now the outer loop counter comes to 2nd row i.e 2, 50. It passes value to the inner query, in this case count does not satisfy 3 >= criteria, hence 50 is not selected.
Now 400, in this case inner query returns 4 and hence it satisfies the criteria, hence 400 is selected
Now 200, in this case inner query returns 3,hence this is also selected
Now 150, in this case inner query returns 2, hence this has been filtered out
Hence the result will be 400, 300, 200 is selected.

Mikhail · Answer 2 · 2013-11-19T12:54:14.347

2

It's a strange way to do this, but it will work. Basically, for each row of table emp, it counts the number of salaries in this table, which are bigger when given, in the subquery:

select count(distinct sal)
       from emp b
     where a.sal <= b.sal

And if number of such salaries is not bigger than three:

3>= (select count(distinct sal)
       from emp b
     where a.sal <= b.sal)

Then it's one of three biggest salaries.

Well, the easiest way would be something like this:

SELECT RES.SAL FROM (SELECT DISTINCT SAL FROM EMP ORDER BY 1 DESC) RES
WHERE ROWNUM <= 3

edited Nov 19 '13 at 12:54

answered Nov 19 '13 at 12:48

Mikhail

1,540
2
13
13

1

It's also guaranteed to bring your server to its knees when the table grows in size. – Twinkles Nov 19 '13 at 12:49
@Twinkles Yes, it sure will :) – Mikhail Nov 19 '13 at 12:52
@SaiDattu Updated an answer. – Mikhail Nov 19 '13 at 12:54
Look at answer here for another way. http://stackoverflow.com/questions/10159495/oracle-sql-query-for-getting-top-3-salaries-rownum-greater-than – Daniel E. Nov 19 '13 at 12:56
@Mikhail .. suppose if i want to get only 3rd max sal..in that case we cant use rownum.. any suggetions please – Sai Nov 20 '13 at 08:19
@SaiDattu Just change condition to ROWNUM = 3 – Mikhail Nov 20 '13 at 11:30

score 2 · Answer 3 · edited May 23 '17 at 12:06

Basically your friend is suggesting you a Relational Algebra way of dealing with the max attribute problem. Check it out (How can I find MAX with relational algebra?) or google it

Firstly, this query will help you to find out the max salary

select sal from emp b where a.sal <= b.sal

Secondly, the inside aggregate function is to count how many occurrence of the max salary

count(distinct sal)

Finally, the 3>= is basically to check whether it has occurrence of the max salary more than three times. So if the table had less than three employees all having the max salary, no result would be found.

3>= (select count(distinct sal) from emp b where a.sal <= b.sal) ;

score 0 · Answer 4 · answered Nov 19 '13 at 12:57

0

A better option:

SELECT sal
FROM  (SELECT sal,RANK() OVER (ORDER BY sal DESC) r FROM stats)
WHERE R <= 3

answered Nov 19 '13 at 12:57

Twinkles

1,984
1
17
31

score 0 · Answer 5 · edited Jan 29 '15 at 12:33

0

Another option using ROW_NUMBER function :

SELECT * FROM TABLE_NAME QUALIFY ROW_NUMBER OVER(ORDER BY SAL DESC) <=3

edited Jan 29 '15 at 12:33

Lorenz Meyer

19,166
22
75
121

answered Jan 29 '15 at 12:05

Blue Bird

193
3
8

score 0 · Answer 6 · edited May 06 '15 at 18:44

0

---to find 2 max salarised emp details

select * from emp_data a
where 2 >= (select COUNT(distinct sal)
            from emp_data b
            where a.sal <= b.sal)

very simple one and easiest

edited May 06 '15 at 18:44

Jan Zahradník

2,417
2
33
44

answered May 06 '15 at 18:09

rehana

1
1

score 0 · Answer 7 · answered Apr 21 '20 at 07:00

0

the easiest way would be something like this:

select distinct salary from employees order by salary desc LIMIT 3;

The above statement performed for "N" of salaries.

If you need a salary of employees from n1_maximum to n2_maximum,then the following query can be used.

select distinct salary from employees order by salary desc LIMIT 3 OFFSET 4;

answered Apr 21 '20 at 07:00

Crypterdine

1

The solution you have provided is an alternative solution. However it doesn't explains Sai's query. – zero Apr 21 '20 at 08:01

Explanation of the query for getting the 3 maximum salaries

7 Answers7