Youngest and oldest employee rank()

I have set up below that’s working but I am trying to change it to use rank() and I’m running into various syntax errors. I was hoping someone could help me out.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

CREATE TABLE EMPLOYEES 
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DOB) AS 
SELECT 1, 'John', 'Doe', DATE '2002-06-01'
 FROM DUAL UNION ALL 
SELECT 2, 'Jane', 'Doe', DATE '2002-08-09'
 FROM DUAL UNION ALL 
SELECT 3, 'Mike', 'Jones', DATE '2000-08-09'
 FROM DUAL; 

 SELECT * FROM EMPLOYEES WHERE DOB IN (SELECT MIN(DOB) FROM EMPLOYEES UNION ALL SELECT MAX(DOB) FROM EMPLOYEES)

>Solution :

You can use rank() twice, ordering by DOB ascending and descending:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DOB,
  RANK() OVER (ORDER BY DOB DESC) AS RNK_LOW,
  RANK() OVER (ORDER BY DOB) AS RNK_HIGH
FROM EMPLOYEES

And then use that as a subquery (CTE or inline view) and filter the results:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DOB
FROM (
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DOB,
    RANK() OVER (ORDER BY DOB DESC) AS RNK_LOW,
    RANK() OVER (ORDER BY DOB) AS RNK_HIGH
  FROM EMPLOYEES
)
WHERE RNK_LOW = 1
OR RNK_HIGH = 1

fiddle

If more than one person has the same earliest or latest DOB then this will show both, like your original query.