CODEWITHSUNDEEP
mysqlsql
Birju
Birju

Reputation: 107

Not able to understand the query

I wanted to find two maximum salaries from every department in a table which had department no., salary, and various other columns. I got this answer; it surely works but I am not able to understand the logic.

select * 
from emp a where 2 > (select count( distinct(sal)) 
                      from emp 
                      where sal > a.sal and a.deptno=deptno)
order by deptno;

Upvotes: 4

Views: 102

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

Relocate the subquery to the SELECT clause without the 'top 2' restriction (will obviously get more rows back):

        select a.*, 
               (
                select count( distinct(sal)) 
                  from emp 
                 where sal > a.sal and a.deptno=deptno
               ) as tally
          from emp a

You can then restrict the resultset using a WHERE clause introducing a further level e.g. 

select b.* 
  from (
        select a.*, 
               (
                select count( distinct(sal)) 
                  from emp 
                 where sal > a.sal and a.deptno=deptno
               ) as tally
          from emp a
       ) b     
 where b.tally < 2
 order 
    by b.deptno, b.tally;

The above is more verbose but maybe easier to follow the logic.

Upvotes: 0

Ralph R&#246;&#223;ner
Ralph R&#246;&#223;ner

Reputation: 93

The inner select returns the number of higher salaries within the same department for a given employee. Now if there are less than two higher salaries within the same department then the given employee must be the top earning or next-to-top earning person within the department.

Upvotes: 2

Steve Wilkes
Steve Wilkes

Reputation: 7135

For each row in employee, the query within the WHERE clause counts how many rows have a higher salary in the same department. The WHERE clause itself then restricts the results to only those salaries which have 1 or 0 rows (2 >) in the same department with a greater salary - i.e. the highest two salaries.

So with this data:

EmployeeId   Sal   DeptNo   No. of rows in the same department with higher salary
         1     1        1   3 (employees 2, 3 and 4)
         2     2        1   2 (employees 3 and 4)
         3     3        1   1 (employee 4)
         4     4        1   0
         5     1        2   2 (employees 6 and 7)
         6     2        2   1 (employee 7)
         7     3        2   0

...the query will select employees 3, 4, 6 and 7, as they're the employees with fewer than 2 employees who have a higher salary than them.

Upvotes: 5

Related Questions