How to effectively limit a selection size in a generic manner in Oracle?
SELECT *
FROM (
  SELECT *
  FROM FOO f
  WHERE f.bar &gt; baz
  ORDER BY f.qux
)
WHERE ROWNUM &lt;= 1;


Means: order the resultset by f.qux then return the 1st row based on that order.

SELECT *
FROM FOO f
WHERE f.bar &gt; baz
    AND ROWNUM &lt;= 1
ORDER BY f.qux;


Means: get the 1st row by unspecified order, then order the result. Order by has no effect at all in this specific example.

They are different.

Use the first one, or starting with 12c, you can use the below:

SELECT *
FROM FOO f
WHERE f.bar &gt; baz
ORDER BY f.qux
FETCH FIRST 1 ROW ONLY; -- FETCH FIRST 1 ROWS ONLY, FETCH FIRST n ROWS ONLY also works