sql - How to use Oracle ORDER BY and ROWNUM correctly?

Question

I am having a hard time converting stored procedures from SQL Server to Oracle to have our product compatible with it.

I have queries which returns the most recent record of some tables, based on a timestamp :

SQL Server:

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

=> That will returns me the most recent record

But Oracle:

SELECT *
FROM raceway_input_labo 
WHERE  rownum <= 1
ORDER BY t_stamp DESC

=> That will returns me the oldest record (probably depending on the index), regardless the ORDER BY statement!

I encapsulated the Oracle query this way to match my requirements:

SELECT * 
FROM 
    (SELECT *
     FROM raceway_input_labo 
     ORDER BY t_stamp DESC)
WHERE  rownum <= 1

and it works. But it sounds like a horrible hack to me, especially if I have a lot of records in the involved tables.

What is the best way to achieve this ?

Answer 1

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.