1

I am trying to write a query to get information about the employees who hired first in each department. I am writing the query below and I get

"ORA-00904: "RN": invalid identifier 00904. 00000 - "%s: invalid identifier"

Query:

select employee_id, department_id, first_name, last_name, hire_date, 
       ROW_NUMBER () over (partition by department_id order by hire_date) as rn
from employees
where rn =1;

Can you please explain to me what is wrong with the "rn" identifier?

GhostCat
  • 137,827
  • 25
  • 176
  • 248
Marios Christodoulou
  • 11
  • 3
  • Add query as text not as link to an image – Jens Apr 18 '17 at 12:59
  • This is an order of operation issue. The SELECT is executed after the where clause by the engine. So at the time of execution, RN is unknown to the where clause. To resolve use a subquery or CTE as @GordonLinoff has illustrated. [More info on a prior stack question](http://stackoverflow.com/questions/17403935/what-is-the-order-of-execution-for-this-sql-statement) – xQbert Apr 18 '17 at 13:10

1 Answers1

2

You need to use a subquery or CTE:

select e.*
from (select employee_id, department_id, first_name, last_name, hire_date, 
             ROW_NUMBER () over (partition by department_id order by hire_date) as rn
      from employees e
     ) e
where rn = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786