Differences

This shows you the differences between two versions of the page.

--- kurs:select_mit_where_in_subquery [2014/09/10 21:22]
127.0.0.1 external edit
+++ kurs:select_mit_where_in_subquery [2017/03/20 11:28] (current)
admin
@@ Line 7: / Line 7: @@
 and e.department_id = d.department_id
 order by salary
+</code>
+Alternativ und evtl besser verständlich
+<code>
+with max_sal_per_dep as
+(
+select department_id,
+       max(salary) max_sal
+  from employees
+  group by department_id
+)
+select first_name,
+       last_name,
+       salary,
+       e.department_id
+from employees e join max_sal_per_dep m
+  on e.department_id = m.department_id and
+     e.salary = m.max_sal
+</code>
+<code>
 select last_name, first_name, department_id, salary from employees
 where salary in (select min(salary) from employees group by department_id)

dokuwiki