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

on

 

To limit the number of rows returned by an Oracle query after ordering, you can use the FETCH FIRST or ROWNUM technique, depending on your Oracle database version. Here's an example using both methods:

Using FETCH FIRST (Oracle 12c and later):

sql
SELECT column1, column2
FROM your_table
ORDER BY column1
FETCH FIRST 10 ROWS ONLY;

In this example:

  • your_table should be replaced with the name of your table.
  • column1 and column2 should be replaced with the columns you want to retrieve.
  • ORDER BY column1 specifies the column by which you want to order the results.
  • FETCH FIRST 10 ROWS ONLY limits the result set to the top 10 rows based on the ordering specified.

Using ROWNUM (Oracle 11g and earlier):

sql
SELECT *
FROM (
    SELECT column1, column2, ROWNUM AS rnum
    FROM your_table
    ORDER BY column1
)
WHERE rnum <= 10;

In this example:

  • your_table should be replaced with the name of your table.
  • column1 and column2 should be replaced with the columns you want to retrieve.
  • ORDER BY column1 specifies the column by which you want to order the results.
  • The inner subquery assigns a unique row number to each row using ROWNUM and aliases it as rnum.
  • The outer query then filters rows where rnum is less than or equal to 10, effectively limiting the result set to the top 10 rows based on the ordering.

Choose the method that corresponds to your Oracle database version. The FETCH FIRST method is more straightforward and recommended for Oracle 12c and later versions, while the ROWNUM method can be used for Oracle 11g and earlier versions.

Comments

Post a Comment