Differences

This shows you the differences between two versions of the page.

--- oracle-to-mysql:offset_fetch_first [October 14, 2024 12:51 pm]
sqlines
+++ oracle-to-mysql:offset_fetch_first [November 26, 2024 10:24 am] (current)
sqlines
@@ Line 1: / Line 1: @@
+====== 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. {{:exclamation.png|}}
+Consider the following sample table:
+<code sql>
+  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);
+</code>
+**Oracle**:
+<code sql>
+  -- 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
+</code>
+**MySQL**:
+<code sql>
+  -- 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
+</code>
+===== PERCENT Clause =====
+In Oracle PERCENT clause allows you to specify the percentage of rows. This is not supported by MySQL LIMIT clause: {{:exclamation.png|}}
+**Oracle**:
+<code sql>
+  -- Select 10 percent of rows
+  SELECT name FROM products ORDER BY category FETCH FIRST 10 PERCENT ROWS ONLY;
+  # Apple
+</code>
+===== WITH TIES Clause =====
+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: {{:exclamation.png|}}
+**Oracle**:
+<code sql>
+  -- 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
+</code>
+===== OFFSET Only =====
+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: {{:exclamation.png|}}
+**Oracle**:
+<code sql>
+  SELECT name FROM products ORDER BY category OFFSET 2 ROWS;
+  # Orange
+</code>
+**MySQL**:
+<code sql>
+  SELECT name FROM products ORDER BY category LIMIT 2, 1000000;
+  # Orange
+</code>
+For more information, see [[/oracle-to-mysql|Oracle to MySQL Migration]].
+~~NOTOC~~

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications

Differences