How to use Oracle ORDER BY and ROWNUM correctly?

sql

prompt-engineering

best-practices

performance

byAnton Shumikhin·Sep 27, 2024

Employ the subquery method in Oracle to efficiently couple ORDER BY and ROWNUM. First, sort your data within the subquery, after which you can apply the ROWNUM filter:

SELECT * FROM ( 
  -- Sorting within the subquery
  SELECT col1, col2, ROWNUM AS rn
  FROM your_table
  ORDER BY col1, col2 DESC  
) 
WHERE rn <= 10;  -- Applying the ROWNUM filter

In this manner, your top 10 entries are neatly arranged within the subquery before the application of the row number limit.

Navigating the quirks: ORDER BY, ROWNUM, and WHERE clause

Order matters in Oracle's SQL - a crucial point when combining ORDER BY and ROWNUM. The WHERE clause is executed before ORDER BY, so if you're using ROWNUM without a subquery, your results may give you a surprise!

The order of operations

The key to understanding the correct use of ROWNUM in conjunction with ORDER BY lies in knowing that ROWNUM is allocated prior to ordering the rows. Therefore, encapsulating your query in a subquery is not just a good idea, it's a necessity. Without the subquery, you'll get unexpected results, much like ordering a salad and getting a "surprise" dessert instead (and we don't mean this in a good way)!

-- Don't do this at home, kids!
SELECT ROWNUM, col1
FROM your_table
WHERE ROWNUM <= 10
ORDER BY col1;

In this query, ROWNUM is assigned first, and the ORDER BY clause has no effect on it, leading to unforeseen and unsorted results.

Look beyond ROWNUM

If you are using Oracle 12c or later, consider the FETCH FIRST or OFFSET row limiting clauses. They give your code a tidy, contemporary finish:

SELECT col1, col2
FROM your_table
ORDER BY col1 DESC  -- Sorting by descending order; because sometimes you want your data served in a topsy-turvy way.
FETCH FIRST 10 ROWS ONLY;  -- The "modern" way to say "give me only the top 10, please".

The timestamp tango

To retrieve the most recent record, skip the sorting exercise altogether. You can simply use an aggregate function like MAX() inside a subquery. It's kind of like time travel, but just for your data:

SELECT t.*
FROM (
  SELECT MAX(t_stamp), col1
  FROM your_table
  GROUP BY col1
) subq
JOIN your_table t ON t.t_stamp = subq.MAX(t_stamp);

Mind your resources

Just like a buffet, where taking too much food is wasteful, querying unnecessary columns in Oracle can lead to wasted resources. Conveniently select individual columns instead of SELECT * when you can:

SELECT col1, col2, ROWNUM AS rn
FROM your_table 
ORDER BY col1, col2 DESC

Reducing Time Travel: Embracing TO_DATE and Manual Limitations

Mastering TO_DATE

Oracle's TO_DATE function can act as your time machine, enabling efficient limitation of results by timestamp:

SELECT col1
FROM (
  SELECT col1, col2, ROWNUM AS rn
  FROM your_table
  WHERE col2 >= TO_DATE('2023-01-01', 'YYYY-MM-DD')  -- Happy New Year, 2023!
  ORDER BY col2
)
WHERE rn <= 10;

Working with manual result limitations

When you need to retrieve data between specific times, a custom WHERE clause within an inner query offers increased performance:

SELECT col1, col2
FROM (
  SELECT col1, col2
  FROM your_table
  ORDER BY col2 DESC  -- Back to the future!
)
WHERE col2 BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-01-31', 'YYYY-MM-DD');