OFFSET and FETCH FIRST - Row Limiting - Oracle to MySQL Migration

In Oracle 12c and later you can use OFFSER and FETCH FIRST (or NEXT) clause to limit the rows returned by the query. In MySQL you can use LIMIT clause, but it does not support all Oracle clauses, see below.

Consider the following sample table:

  CREATE TABLE products (name VARCHAR(30), category INT);
 
  INSERT INTO products VALUES ('Apple', 1);
  INSERT INTO products VALUES ('Melon', 1);
  INSERT INTO products VALUES ('Orange', 2);

Oracle:

  -- Select 1 row only
  SELECT name FROM products ORDER BY category FETCH FIRST 1 ROWS ONLY;  
  # Apple
 
  -- Select 1 row only but with offset 2
  SELECT name FROM products ORDER BY category OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY;
  # Orange

MySQL:

  -- Select 1 row only
  SELECT name FROM products ORDER BY category LIMIT 1;
 
   -- Select 1 row only but with offset 2
  SELECT name FROM products ORDER BY category LIMIT 2, 1;
  # Orange

In Oracle PERCENT clause allows you to specify the percentage of rows. This is not supported by MySQL LIMIT clause:

Oracle:

  -- Select 10 percent of rows
  SELECT name FROM products ORDER BY category FETCH FIRST 10 PERCENT ROWS ONLY;  
  # Apple

When WITH TIES clause is specified the query returns all rows with the same key as the last row fetched. This is not supported by MySQL LIMIT clause:

Oracle:

  -- Asked to return 1 row, but 2 are returned as the key is the same
  SELECT name FROM products ORDER BY category FETCH FIRST 1 ROW WITH TIES;  
  # Apple
  # Melon

Oracle allows you to specify OFFSET clause without FETCH FIRST clause. In MySQL the offset is optional, but row limit must be specified, so you have to specify some large number:

Oracle:

  SELECT name FROM products ORDER BY category OFFSET 2 ROWS;  
  # Orange

MySQL:

  SELECT name FROM products ORDER BY category LIMIT 2, 1000000;  
  # Orange

For more information, see Oracle to MySQL Migration.