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  lt   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  lt   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

User · Accepted Answer

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

User · Answer

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  lt   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  lt   3    DEPTNO    ENAME    RNO ------------------------- 10    CLARK        1 10    MILLER       2 10    KING         3

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to sql-order-by

Examples related to rownum