FETCH FIRST n ROWS ONLY - IBM DB2 to Oracle Migration

In DB2, you can use FETCH FIRST n ROWS ONLY clause in a SELECT statement to return only n rows, and this limit is applied after sorting the rows as specified in the ORDER BY clause.

IBM DB2:

  -- Sample table
  CREATE TABLE cities (name VARCHAR(70));
 
  -- Sample data
  INSERT INTO cities VALUES ('Paris');
  INSERT INTO cities VALUES ('New York');
  INSERT INTO cities VALUES ('Prague');
  INSERT INTO cities VALUES ('Alanya'); 
  INSERT INTO cities VALUES ('London');

Let's use FETCH FIRST 3 ROWS ONLY without ORDER BY:

  -- Rows are not ordered
  SELECT name FROM cities FETCH FIRST 3 ROWS ONLY;

Result:

Now let's use FETCH FIRST 3 ROWS ONLY with ORDER BY:

  -- Rows are ordered now
  SELECT name FROM cities ORDER BY name FETCH FIRST 3 ROWS ONLY;

Result:

Note that starting from Oracle 12c you can also use FETCH FIRST clause in Oracle, so the conversion is not required.

Prior Oracle 12c you can use the ROWNUM pseudo-column to limit the number of retrieved rows, but it is applied before sorting, so you have to use a sub-query in order to limit the number of rows after sorting.

If there is no ORDER BY clause in the original DB2 query, you can just add ROWNUM condition as follows:

Oracle:

  -- Rows are not ordered
  SELECT name FROM cities WHERE rownum <= 3;

Result:

But if you need to sort rows before applying ROWNUM, you have to use a subquery:

  -- Rows are ordered now
  SELECT * 
  FROM (SELECT name FROM cities ORDER BY name)
  WHERE rownum <= 3;

Result:

For more information, see

• IBM DB2 to Oracle Migration Tools and Reference