Assuming that you are inserting data with an incrementing value for the primary key (via a sequence or a date/timestamp value) then you can do:
SELECT *
FROM (
SELECT *
FROM table_name
ORDER BY primary_key_column
)
WHERE ROWNUM <= 10;
You need the outer query as the order in which execution happens in a query is:
- The
WHERE
clause filters are applied (and the ROWNUM
pseudo-column is generated for each row that matches all the WHERE
clause filters);
- Then the
ORDER BY
clause is applied.
Applying this in a single query will get the first 10 rows found in the database and will then order those rows by the primary key (not what you want). Using an inner query you can force the ORDER BY
clause to be applied first and then the filtering on the required number of rows will occur subsequently in the execution of the outer query.
If you don't have an incrementing primary key then you will have to rely on just ROWNUM
without any ORDER BY
clause - however, if the table has row movement enabled or if you delete a row and then insert a different row (in which case the database may fill the empty space with the new row) then you may find rows are not retrieved in insertion order. If you can live with this then just do:
SELECT *
FROM table_name
WHERE ROWNUM <= 10;