Selecting a grouping subset

Find out the employees born on the same days.

SQL

SELECT * FROM employee
WHERE to_char(birthday,'MMDD') IN
     (SELECT to_char(birthday,'MMDD')
     FROM employee
     GROUP BY to_char(birthday,'MMDD') HAVING count(*)>1)

Sometimes the purpose of grouping rows is to obtain the grouping subsets, not the summary value. As SQL cannot store the grouping result, the user will need to change the way of thinking to query again from the source set.

SPL

A
1 =demo.query("select * from employee").group(month(birthday),day(birthday))
2 =A1.select(~.len()>1).conj()

SPL can retain the grouping result sets, and it handles the result sets like the other normal 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.