Completing the result set step by step

Select two employees (one male and the other female) from each department as a pair to play games.

SQL

WITH A AS
   (SELECT name,dept,
       row_number() OVER(PARTITION BY dept ORDER BY 1)
        sequence_no FROM employee WHERE gender='male'),
     B AS
    (SELECT name,dept,
        row_number() OVER(PARTITION BY dept ORDER BY 1)
        sequence_no FROM employee WHERE gender='female')
SELECT name,dept FROM A
   WHERE dept IN ( SELECT DISTINCT dept FROM B ) AND sequence_no=1
UNION ALL
SELECT name,dept FROM B
    WHERE dept IN ( SELECT DISTINCT dept FROM A ) AND sequence_no=1

Sometimes, it is not difficult to get the result set step by step using a particular algorithm. However, SQL does not support the stepwise computation, forcing the user to figure out a way very different from their natural mental process to complete the computation in one statement. It is not only difficult to understand, but also inefficient to compute.

SPL

A B C
1 =[ ] =demo.query("select * from employee").group(dept) Result is stored in A1
2 for B1 =A2.group@1(gender) Loop through each group to pick a pair (one male, one female)
3 =if B2.len()>1 >A1=A1|B2 Add to the result set when a pair is picked.

With support for stepwise computation and program logics, SPL allows users to compute the result by steps they naturally understand.

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.