Find records with the maximum and the minimum

Find out the interval (days) between the joining date of the oldest employee and the joining date of the youngest employee.

SQL

SELECT
    (SELECT join_date FROM employee
    WHERE birthday=(SELECT min(birthday) FROM employee)) -
    (SELECT join_date FROM employee
    WHERE birthday=(SELECT max(birthday) FROM employee))
FROM dual

The SQL max/min functions cannot retrieve records directly. To get the desired records, you need to query data twice.

SPL

A
1 =demo.query(“select * from employee”)
2 =A1.minp(birthday) The oldest employee
3 =A1.maxp(birthday) The youngest employee
4 =interval(A2.join_date,A3.join_date)

SPL provides various ways to get a record and its position, so it can perform this type of computations very conveniently.

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.