How to limit the number of rows returned by an Oracle query after ordering?

Using ROWNUM to limit the number of rows in Oracle

You can limit the number of rows returned by an Oracle query by using the ROWNUM pseudo-column along with the WHERE clause. The ROWNUM pseudo-column assigns a unique number to each row returned by a query, starting with 1. You can use this pseudo-column to limit the number of rows returned in your query by specifying a range in the WHERE clause.

Here's an example that returns the first 10 rows after ordering the result set by a specific column:

SELECT *
FROM your_table
WHERE ROWNUM <= 10
ORDER BY column_name;

Note that the ROWNUM pseudo-column is assigned to each row before the ORDER BY clause is executed, so the order of the rows in the result set may not match the order specified in the ORDER BY clause. To ensure that the rows are ordered correctly, you can use the ROW_NUMBER function, which allows you to assign a unique number to each row in the result set after ordering.

Here's an example that uses the ROW_NUMBER function to limit the number of rows returned after ordering:

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY column_name) AS row_num
  FROM your_table
)
WHERE row_num <= 10;