Oracle select n rows

Oracle’s way of limiting rows changed significantly in version 12. Before that, you had to wrap your query in a subquery with ROWNUM. Oracle 12 introduced the ANSI-standard FETCH FIRST syntax which is much cleaner. If you’re on 11g or earlier, the subquery approach is the only option.

• Oracle 11 or before

SELECT * FROM (SELECT COLUMN1, COLUMN2 FROM TABLE_NAME ORDER BY COLUMN1 desc) WHERE rownum =1;

• Oracle 12 or above

SELECT COLUMN1, COLUMN2 FROM TABLE_NAME ORDER BY COLUMN1 desc fetch first 1 rows only;

Oracle Blog