05/03/2013

Oracle SELECT TOP

When working with Oracle, you may need to select the TOP row (or top N rows) from a query result, but you'll find there is no such statement.

While you can certainly use ROWNUM, you should note that it is executed BEFORE any ORDER BY statement, thus producing wrong results in that case. The solution in this case is as simple as:

SELECT *
FROM (SELECT * 
        FROM myTable
        ORDER BY myColumn)
WHERE ROWNUM = 1;

By encapsulating your query with the ORDER BY clause inside another query using ROWNUM, you now effectively order the dataset before extracting the TOP row. You may obviously use it as TOP N by setting another value for ROWNUM.

No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.