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

Answer 1

   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:

1. It fetches 1st row, which is 1, 300 and passes this value to the inner query
2. The inner query tries to find a distinct sal value that is less than or equal to the records in emp table b
3. 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.
4. 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.
5. Now 400, in this case inner query returns 4 and hence it satisfies the criteria, hence 400 is selected
6. Now 200, in this case inner query returns 3,hence this is also selected
7. Now 150, in this case inner query returns 2, hence this has been filtered out
8. Hence the result will be 400, 300, 200 is selected.

Answer 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

Answer 3

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) ;

Answer 4

A better option:

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

Answer 5

Another option using ROW_NUMBER function :

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

Answer 6

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

Answer 7

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;