[sql] How to use Oracle ORDER BY and ROWNUM correctly?

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 ?

Documented couple of design issues with this in a comment above. Short story, in Oracle, you need to limit the results manually when you have large tables and/or tables with same column names (and you don't want to explicit type them all out and rename them all). Easy solution is to figure out your breakpoint and limit that in your query. Or you could also do this in the inner query if you don't have the conflicting column names constraint. E.g.

WHERE m_api_log.created_date BETWEEN TO_DATE('10/23/2015 05:00', 'MM/DD/YYYY HH24:MI') 
                                 AND TO_DATE('10/30/2015 23:59', 'MM/DD/YYYY HH24:MI')  

will cut down the results substantially. Then you can ORDER BY or even do the outer query to limit rows.

Also, I think TOAD has a feature to limit rows; but, not sure that does limiting within the actual query on Oracle. Not sure.

An alternate I would suggest in this use case is to use the MAX(t_stamp) to get the latest row ... e.g.

select t.* from raceway_input_labo t
where t.t_stamp = (select max(t_stamp) from raceway_input_labo) 
limit 1

My coding pattern preference (perhaps) - reliable, generally performs at or better than trying to select the 1st row from a sorted list - also the intent is more explicitly readable.
Hope this helps ...

SQLer

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