Analytic query trying to solve-CodePudding

im solving the following task with analytic functions and im stuck.

task: Write a query that shows the latest hired employee per department. In case of ties, use the lowest employee ID.

select  a.EMPLOYEE_ID,
        a.DEPARTMENT_ID,
        a.FIRST_NAME,
        a.LAST_NAME,
        a.HIRE_DATE,
        a.JOB_ID
from (select ROW_NUMBER() over (PARTITION by department_id order by hire_date desc)    
from hr.EMPLOYEES a) A
where A = 1 ;

CodePudding user response：

You need to include the columns you want to select in the outer query in the SELECT clause of the inner query and need to give an alias to the ROW_NUMBER computed value:

select  EMPLOYEE_ID,
        DEPARTMENT_ID,
        FIRST_NAME,
        LAST_NAME,
        HIRE_DATE,
        JOB_ID
from (
  select EMPLOYEE_ID,
         DEPARTMENT_ID,
         FIRST_NAME,
         LAST_NAME,
         HIRE_DATE,
         JOB_ID,
         ROW_NUMBER() over (PARTITION by department_id order by hire_date desc) AS rn
  from   hr.EMPLOYEES
)
where  rn = 1 ;

You still need to address the second part of the question:

In case of ties, use the lowest employee ID.

However, since this appears to be a homework question, I'll leave that for you to solve.