I have this query
SELECT EMPNO, ENAME, HIREDATE, DEPTNO, ROWNUM AS rn
FROM (SELECT EMPNO, ENAME, HIREDATE, DEPTNO FROM EMP ORDER BY HIREDATE DESC)
WHERE rn < 3
but the error is:
ORA-00904: "RN": invalid identifier 00904. 00000 - "% s: invalid identifier" * Cause:
* Action: Error in the line: 34, column: 7
How can I make it work that way or another?
I do not use Oracle, but I think I have an idea.
Try changing the where rn to where rownum , like this:
SELECT EMPNO, ENAME, HIREDATE, DEPTNO, ROWNUM AS rn
FROM (SELECT EMPNO, ENAME, HIREDATE, DEPTNO FROM EMP ORDER BY HIREDATE DESC)
WHERE ROWNUM < 3
If it does not work for you, you can try this other solution, which involves wrapping the whole query in another, like this:
Select EMPNO, ENAME, HIREDATE, DEPTNO, rn from
(
SELECT EMPNO, ENAME, HIREDATE, DEPTNO, ROWNUM AS rn
FROM (SELECT EMPNO, ENAME, HIREDATE, DEPTNO FROM EMP ORDER BY HIREDATE DESC)
)
WHERE rn < 3