How to Efficiently Retrieve the Top Row from an Oracle Table Using `ORDER BY` and `ROWNUM`?

Using Oracle ORDER BY and ROWNUM Efficiently

In Oracle, the ROWNUM pseudocolumn provides a way to retrieve specific rows from a result set based on their sequence. However, using ROWNUM with ORDER BY can lead to unexpected results.

To retrieve the most recent record from a table, the following SQL query is commonly used in SQL Server:

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

However, in Oracle, using this approach with ROWNUM as in the following query:

SELECT *
FROM raceway_input_labo 
WHERE  rownum <= 1
ORDER BY t_stamp DESC

will retrieve the oldest record instead of the most recent. This is because ROWNUM is evaluated before ORDER BY in Oracle.

To effectively retrieve the most recent record using Oracle's ROWNUM, employ subqueries as follows:

SELECT * 
FROM 
    (SELECT *
     FROM raceway_input_labo 
     ORDER BY t_stamp DESC)
WHERE  rownum <= 1

This subquery approach ensures that the ORDER BY is applied first, resulting in the desired output.

Alternatively, a more versatile solution that works in both SQL Server and Oracle can be implemented using the row_number() function:

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 above is the detailed content of How to Efficiently Retrieve the Top Row from an Oracle Table Using `ORDER BY` and `ROWNUM`?. For more information, please follow other related articles on the PHP Chinese website!