By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE employees (last_name, hire_date, department_id) AS
SELECT 'Alice', DATE '2000-01-01', 90 FROM DUAL UNION ALL
SELECT 'Beryl', DATE '2001-01-01', 90 FROM DUAL UNION ALL
SELECT 'Carol', DATE '2002-01-01', 80 FROM DUAL UNION ALL
SELECT 'Debra', DATE '2003-01-01', 90 FROM DUAL;
4 rows affected
SELECT last_name
FROM (
SELECT last_name,
hire_date,
department_id
FROM employees
WHERE department_id IN (80, 90)
)
MATCH_RECOGNIZE(
ORDER BY hire_date
ALL ROWS PER MATCH
PATTERN (^ department_90+ )
DEFINE department_90 AS department_id = 90
)
| LAST_NAME |
|---|
| Alice |
| Beryl |
SELECT last_name
FROM (
SELECT last_name,
COUNT(CASE department_id WHEN 80 THEN 1 END) OVER (ORDER BY hire_date)
AS num_80
FROM employees
WHERE department_id IN (80, 90)
)
WHERE num_80 = 0;
| LAST_NAME |
|---|
| Alice |
| Beryl |