ROWNUM Pseudo-column - Oracle to MySQL Migration

In Oracle, the ROWNUM pseudo-column returns the order number of rows selected from a table. It is usually used to limit the number of rows returned by a query, but it is applied before ORDER BY.

You can use LIMIT clause in MySQL.

Oracle:

  -- Select 1 row only
  SELECT * FROM emp WHERE rownum = 1;
 
  -- Select 3 rows
  SELECT * FROM emp WHERE rownum <= 3;
  SELECT * FROM emp WHERE rownum < 4;

MySQL:

  -- Select 1 row only
  SELECT * FROM emp LIMIT 1;
 
  -- Select 3 rows
  SELECT * FROM emp  LIMIT 3;

In Oracle, you can specify any expression for ROWNUM:

Oracle:

  -- Specifying expression
  SELECT * FROM emp WHERE rownum <= CAST(SUBSTR('12', 1, 1)  AS INT);
  # Ok

While MySQL allows you to use only an integer number (or an integer variable if used in a stored procedure i.e.), and no expressions are allowed.

MySQL:

  -- Specifying expression
  SELECT * FROM emp LIMIT 1*5;
  # ERROR 1064 (42000): You have an error in your SQL syntax;

For more information, see Oracle to MySQL Migration.