How to use Oracle ORDER BY and ROWNUM correctly?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP



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!


ORDER BY



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 ?





On ROWNUM and Limiting Results
– John Woo
Feb 26 '13 at 14:41






What you have done in your last Query is correct. You select the 1st row of an ordered list of records. Simply Query encapsulation.
– araknoid
Feb 26 '13 at 14:49





This is clearly documented in the manual: docs.oracle.com/cd/E11882_01/server.112/e26088/…
– a_horse_with_no_name
Feb 26 '13 at 15:06





@a_horse_with_no_name You mean clearly documented in this 404 error.
– anthonybrice
Jan 28 '15 at 20:14





@anthonybrice: thanks. Oracle changed all their URLs to the manual. The up-to-date link is: docs.oracle.com/cd/E11882_01/server.112/e41084/…
– a_horse_with_no_name
Jan 28 '15 at 20:15





5 Answers
5



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.


where


order by


t_stamp



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.


*



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:


ROW_NUMBER()


ROWNUM


ROW_NUMBER()


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





ROWNUM could be faster than ROW_NUMBER() so whether or not one should use one over the other depends on a number of factors.
– David Faber
Feb 6 '15 at 22:26


ROWNUM


ROW_NUMBER()





Apologies for the downvote it was by mistake! Unfortunately I cannot take it back now.
– Athafoud
May 18 at 12:45



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





There's no LIMIT in Oracle. You are begging the question.
– philipxy
Sep 30 '17 at 2:23



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.



Just use rownum like the following


select *
from (select t.*
from raceway_input_labo ril
order by t_stamp desc
)
where rownum = 1





Is see this was downvoted twice. I know this solution doesn't work well, but I don't know why. Would someone please explain why? Thank you.
– JasonGabler
Jul 12 '17 at 17:23





@JasonGabler t should be ril. They copied that typo from the accepted answer 3 years later. Moreover this code has always been in the question. There's no explanation of "like this", so it doesn't explain anything.
– philipxy
Sep 30 '17 at 1:45


t


ril




Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



Would you like to answer one of these unanswered questions instead?

Popular posts from this blog

Firebase Auth - with Email and Password - Check user already registered

Dynamically update html content plain JS

Creating a leaderboard in HTML/JS