Categories
Coding

How to Select the Top 100 Rows in Oracle SQL

In Oracle SQL, to select the top N rows from a table, you need to use the ROWNUM pseudocolumn. Unlike some other SQL databases, Oracle does not have a specific TOP keyword like SQL Server. Instead, you use a WHERE clause with ROWNUM to limit the number of rows returned.

Here’s how you can select the top 100 rows from a table in Oracle:

SELECT *
FROM your_table
WHERE ROWNUM <= 100;

Replace your_table with the name of your table.

Explanation

  • ROWNUM is a pseudocolumn in Oracle that assigns a unique number to each row returned by the query’s result set. The first row selected by the query has a ROWNUM of 1, the second has 2, and so on.
  • The condition WHERE ROWNUM <= 100 restricts the number of rows returned to 100.

Important Notes

  • Ordering Results: If you want to order the results and then select the top N rows, you need to use a subquery. This is because ROWNUM is applied before ordering in a query. Here’s how you can do it:
    SELECT *
    FROM (
    SELECT *
    FROM your_table
    ORDER BY some_column
    )
    WHERE ROWNUM <= 100;

    In this query, your_table is first ordered by some_column, and then the top 100 rows are selected.

  • Fetch First Clause: In Oracle 12c and later, you can use the FETCH FIRST clause which is more ANSI-standard and readable:
    SELECT *
    FROM your_table
    ORDER BY some_column
    FETCH FIRST 100 ROWS ONLY;

    This is the preferred method in newer versions of Oracle as it is more in line with standard SQL syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *