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.
