SQL - I need to show which employee from department 90 was hired before those from department 80. Oracle SQL.

Database structure -> https://i.stack.imgur.com/RQ6CH.png

I tried this query but it didn't work at all....

SELECT last_name FROM employees WHERE hire_date<ALL (select hire_date FROM employees where(hire_date FROM employees WHERE department_id=90)< (select hire_date FROM employees where(hire_date FROM employees WHERE department_id=80))

CodePudding user response：

This is one option:

• subquery returns the first hiredate for department 80, while
• the main query returns rows for employees in department 90 who were hired before previously returned date

select *
from employees
where department_id = 90
  and hire_date < (select min(hire_date)
                   from employees
                   where department_id = 80
                  )

CodePudding user response：

You can do it without having to query the table twice using a conditional analytic function:

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;

or row-by-row pattern matching using MATCH_RECOGNIZE:

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
);

Which, for the sample data:

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;

Both output:

fiddle