Selecting rows from grouping subsets

Select two employees from each department.


FROM (SELECT employee.*,
         row_number() OVER(PARTITION BY dept ORDER BY 1)
    FROM employee)
WHERE seq_no<=2

To reselect rows from the group subsets, the subquery is necessary for SQL to query from the source set again.


1 =demo.query("select * from employee").group(dept)).conj(~.m([1,2]))

SPL grouping will not disrupt the original order, so it can select the records directly.

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.