[SOLVED] Youngest and oldest employee rank()

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

Your query changed to use windows functions would look like this:

SELECT employee_id, first_name, last_name, dob
FROM
(
  SELECT 
    employee_id, first_name, last_name, dob,
    MIN(dob) OVER () as min_dob,
    MAX(dob) OVER () as max_dob
  FROM employees
)
WHERE dob IN (min_dob, max_dob);

The same with RANK:

SELECT employee_id, first_name, last_name, dob
FROM
(
  SELECT 
    employee_id, first_name, last_name, dob,
    RANK() OVER (ORDER BY dob) as low_to_high,
    RANK() OVER (ORDER BY dob DESC) as high_to_low
  FROM employees
)
WHERE low_to_high = 1 OR high_to_low = 1;