Geek Answers Handbook

Failed to understand the request.

I wanted to find the two maximum salaries from each department in a table in which there is a department number, salary and various other columns. I got this answer; It certainly works, but I cannot 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;
+4
source share
3 answers

For each employee line, the query in the WHERE clause calculates how many lines have a higher salary in one department. Instead, the WHERE clause restricts the results to only those salaries that have 1 or 0 rows ( 2 > ) in the same department with the highest salary — that is, 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 request will select employees 3, 4, 6 and 7, since they are employees with less than two employees who have a higher salary than theirs.

+5
source

Internal selection returns the number of higher salaries in one department for a given employee. Now, if in one department there are less than two higher salaries, then this employee must be a senior or closest borrower in the department.

+2
source

Move the subquery to the SELECT with no “top 2” constraint (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

Then you can limit the result set with the WHERE , which represents an additional level, for example.

 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 detailed, but it may be easier to follow the logic.

0
source

More articles:


All Articles