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 ROWNUM can be also used to generate row numbers. Note that ROWNUM is applied before ORDER BY.

You can use the LIMIT clause and ROW_NUMBER() OVER () function 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;

If the ROWNUM pseudo-column is used in the SELECT expression it generates the row numbers:

Consider a sample table:

  CREATE TABLE colors (name VARCHAR(30));
 
  INSERT INTO colors VALUES ('Green');
  INSERT INTO colors VALUES ('Black');
  INSERT INTO colors VALUES ('Red');

Oracle:

  -- Generate row numbers
  SELECT ROWNUM as RN, name FROM colors;

Result:

But you can see that ROWNUM is applied before ORDER BY clause:

Oracle:

  -- Generate row numbers with ORDER BY
  SELECT ROWNUM as RN, name FROM colors ORDER BY name;

Result:

In MySQL, you can use ROW_NUMBER() OVER() function:

MySQL:

  -- Generate row numbers
  SELECT ROW_NUMBER() OVER () as RN, name FROM colors;

Result:

But note that ROW_NUMBER() OVER () is applied after ORDER BY clause by default:

MySQL:

  -- Generate row numbers with ORDER BY
  SELECT ROW_NUMBER() OVER () as RN, name FROM colors ORDER BY name;

Result:

But using ORDER BY NULL or ORDER BY (SELECT NULL) in ROW_NUMBER function you can get similar behavior with Oracle's ROWNUM and assign row numbers before applying ORDER BY clause of the query:

MySQL:

  -- Generate row numbers before applying ORDER BY of query
  SELECT ROW_NUMBER() OVER (ORDER BY NULL) as RN, name FROM colors ORDER BY name;
 
  -- (SELECT NULL) gives the same result
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RN, name FROM colors ORDER BY name;

Result of both queries:

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.