How does Oracle Rownum

How do I use Oracle ORDER BY and ROWNUM correctly?


I have a hard time converting stored procedures from SQL Server to Oracle for our product to be compatible with.

I have queries that return the latest record of some tables based on a timestamp:

SQL Server:

=> This gives me the latest data set

But Oracle:

=> This gives me the oldest data record (probably depending on the index), regardless of the statement!

I encapsulated the Oracle query to suit my needs:

and it works. But it sounds like a terrible hack to me, especially when I have a lot of records in the tables involved.

What's the best way to do this?






Reply:


The statement will in front that executed. So your desired query is " Take the first line and arrange then after descending ". And you don't intend to.

The subquery method is the correct way to do this in Oracle.

If you want a version that works on both servers, you can use:

The exterior returns "1" in the last column. You would have to list the columns one by one to avoid this.


Use instead. is a pseudocolumn and is a function. You can read up the difference between them and see the difference in the output of the following queries:




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

My coding pattern preference (maybe) - reliable, generally better performing, or better than trying to pick the first row from an ordered list - the intent is also more explicitly readable.
Hope that helps ...

SQLer



Documented some design issues with it in a comment above. In short, in Oracle, if you have large tables and / or tables with the same column names (and you don't want to explicitly enter all of them and rename all of them), you have to manually narrow the results. A simple solution is to find out your breakpoint and limit it in your query. You can do this in the inner query as well if you don't have the conflicting column name constraint. E.g.

will significantly reduce the results. Then you can do ORDER BY or even the outer query to limit rows.

I also think TOAD has a function to limit lines. However, I am not sure if this limits the actual query on Oracle. Not sure.

We use cookies and other tracking technologies to improve your browsing experience on our website, to show you personalized content and targeted ads, to analyze our website traffic, and to understand where our visitors are coming from.

By continuing, you consent to our use of cookies and other tracking technologies and affirm you're at least 16 years old or have consent from a parent or guardian.

You can read details in our Cookie policy and Privacy policy.