CODEWITHSUNDEEP
sqloracle-database
Chen
Chen

Reputation: 383

Select the oldest and the youngest employees in Oracle

I would like to select the oldest and the youngest employees in the workplace. The oldest and the youngest refer to their working experience. I referred to previous questions with SQL server and modified my code as below, but I had the error:

invalid identifier.

I wonder would there be any suggestions for me to change my code?

 SELECT d.Dtable_name,
        RANK() OVER(ORDER BY SUM(ROUND((SYSDATE - e.hire_date) / 365.24,0)) DESC) AS yrsexperience
   FROM Dtable_department d
   LEFT JOIN Etable_employee e
     ON d.department_id=e.department_id
WHERE yrsexperience =(SELECT MAX(d.department_name) KEEP(DENSE_RANK FIRST ORDER BY SUM(ROUND((SYSDATE - e.hire_date) / 365.24,0)) DESC) AS yrsexperience) AS Oldest)FROM Etable_employee e)

OR yrsexperience =(SELECT
       MIN(d.department_name) KEEP(DENSE_RANK LAST  ORDER BY SUM(ROUND((SYSDATE - e.hire_date) / 365.24,0)) DESC) AS yrsexperience) AS Youngest
  FROM Etable_employee e)

Update

I edited the code after referring to the answer below, but it threw me the error:

FROM is not found where expected.

Upvotes: 0

Views: 795

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may use FIRST and LAST aggregate functions.

SELECT MAX(name) KEEP(DENSE_RANK FIRST ORDER BY yrs_experience ) AS Youngest,
       MAX(name) KEEP(DENSE_RANK LAST  ORDER BY yrs_experience ) AS Oldest
  FROM etable_employee;

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

It appears that YRSEXPERIENCE is a field on ETABLE_EMPLOYEE. Given that, I would expect that

SELECT *
  FROM ETABLE_EMPLOYEE
  WHERE YRS_EXPERIENCE IN (SELECT MIN(YRS_EXPERIENCE) FROM ETABLE_EMPLOYEE
                           UNION ALL
                           SELECT MAX(YRS_EXPERIENCE) FROM ETABLE_EMPLOYEE)

would give you what you're looking for.

EDIT

Based on further information:

SELECT SYSDATE - e.HIRE_DATE AS YRS_EXPERIENCE
  FROM ETABLE_EMPLOYEE e
  WHERE SYSDATE - e.HIRE_DATE IN (SELECT MIN(SYSDATE - HIRE_DATE) FROM ETABLE_EMPLOYEE
                                  UNION ALL
                                  SELECT MAX(SYSDATE - HIRE_DATE) FROM ETABLE_EMPLOYEE)

dbfiddle here

Upvotes: 0

Related Questions