How to use Oracle ORDER BY and ROWNUM correctly?
Since Oracle 12c we now have row limiting clauses which do exactly this.
SELECT *
FROM raceway_input_labo
ORDER BY t_stamp DESC
FETCH FIRST ROW ONLY
Or many alternatives for different scenarios (first n rows, tie handling, etc.).
The where
statement gets executed before the order by
. So, your desired query is saying "take the first row and then order it by t_stamp
desc". And that is not what you intend.
The subquery method is the proper method for doing this in Oracle.
If you want a version that works in both servers, you can use:
select ril.*
from (select ril.*, row_number() over (order by t_stamp desc) as seqnum
from raceway_input_labo ril
) ril
where seqnum = 1
The outer *
will return "1" in the last column. You would need to list the columns individually to avoid this.
Use ROW_NUMBER()
instead. ROWNUM
is a pseudocolumn and ROW_NUMBER()
is a function. You can read about difference between them and see the difference in output of below queries:
SELECT * FROM (SELECT rownum, deptno, ename
FROM scott.emp
ORDER BY deptno
)
WHERE rownum <= 3
/
ROWNUM DEPTNO ENAME
---------------------------
7 10 CLARK
14 10 MILLER
9 10 KING
SELECT * FROM
(
SELECT deptno, ename
, ROW_NUMBER() OVER (ORDER BY deptno) rno
FROM scott.emp
ORDER BY deptno
)
WHERE rno <= 3
/
DEPTNO ENAME RNO
-------------------------
10 CLARK 1
10 MILLER 2
10 KING 3