Differences

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

--- kurs:correlated_subquery [2014/09/10 21:22]
127.0.0.1 external edit
+++ kurs:correlated_subquery [2020/06/02 09:26]
admin
@@ Line 45: / Line 45: @@
         and  outer.fhz_name = inner.fhz_name
        )) > 1
+</code>
+<code>
+with emp_per_country as (
+SELECT dep.department_id,
+    department_name,
+    cou.country_id,
+    country_name,
+    COUNT(employee_id) c
+FROM hr.employees emp
+JOIN hr.departments dep
+ON emp.department_id = dep.department_id
+JOIN hr.locations loc
+ON loc.location_id = dep.location_id
+JOIN hr.countries cou
+ON cou.country_id = loc.country_id
+GROUP BY dep.department_id, department_name, cou.country_id, country_name
+ORDER BY COUNT(employee_id) DESC
+)
+select country_name, department_name, c from emp_per_country o
+where c = (select max(c) from emp_per_country where country_id = o.country_id)
 </code>

dokuwiki