Sort the groupings

Find out the department with the most employees and the department with the fewest employees.

SQL

SELECT dept FROM
  (SELECT dept,
          row_number() OVER (ORDER BY total_emp DESC) rank_ASC,
          row_number() OVER (ORDER BY total_emp ASC) rank_DESC
   FROM (SELECT dept, count(*) total_emp
         FROM employee GROUP BY dept))
WHERE rank_ASC=1 OR rank_DESC=1 ORDER BY rank_ASC

The having clause cannot handle the task of filtering the grouping result set that requires inter-row computation. A nested subquery must be used to complete this computation.

SPL

A
1 =demo.query("select * from employee").group(dept)
2 =A1.sort(~.len()).m([-1,1])
3 =A2.(dept)

To SPL, it is again about sorting and filtering the result sets, the only difference is the members of the result set are also sets.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.