How to use Oracle ORDER BY and ROWNUM correctly?

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     ) rilwhere 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        110    MILLER       210    KING         3

Since Oracle 12c we now have row limiting clauses which do exactly this.

SELECT *FROM raceway_input_labo ORDER BY t_stamp DESCFETCH FIRST ROW ONLY

Or many alternatives for different scenarios (first n rows, tie handling, etc.).