Selecting rows from grouping subsets

Select two employees from each department.

SQL

SELECT *
FROM (SELECT employee.*,
         row_number() OVER(PARTITION BY dept ORDER BY 1)
         seq_no
    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.

SPL

A
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.