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 ?
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.