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 return 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 return me the most recent record

But Oracle:

SELECT *

FROM raceway_input_labo 

WHERE  rownum <= 1

ORDER BY t_stamp DESC

=> That will return me the oldest record (probably depending on the index), regardless of 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

In this case, the subquery method will be the proper method for doing this in Oracle.If you need the version that works in both the servers, then you can use the following code:

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.

In the above query (mentioned in the question), the where statement gets executed before the order by. Therefore, your desired query will "take the first row and then order it by t_stamp desc". But, that is not what you intend.