DENSE_RANK Function - Oracle to MySQL Migration

In Oracle to MySQL, the DENSE_RANK window function assigns a rank without gaps when the preceding rows have identical values. But Oracle and MySQL handle NULL values differently.

Consider an example:

  CREATE TABLE colors (name VARCHAR(30));
 
  -- Insert sample rows
  INSERT INTO colors VALUES ('Green');
  INSERT INTO colors VALUES ('Red');
  INSERT INTO colors VALUES (NULL);
  INSERT INTO colors VALUES ('Blue');
  INSERT INTO colors VALUES ('Green');

Oracle:

  -- Assign rank with no gaps, NULL is last
  SELECT name, DENSE_RANK() OVER (ORDER BY name) AS rank FROM colors;

Result:

MySQL:

  -- Assign rank with no gaps, NULL is first (!)
  SELECT name, DENSE_RANK() OVER (ORDER BY name) AS `rank` FROM colors;

Result:

Note that Oracle and MySQL sort NULL values differently, leading to different query results, see ORDER BY NULLS FIRST or LAST for more details.

For more information, see Oracle to MySQL Migration.