DENSE_RANK Function - Oracle to MariaDB Migration

In Oracle to MariaDB, the DENSE_RANK window function assigns a rank without gaps when the preceding rows have identical values. But Oracle and MariaDB 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:

MariaDB:

  -- 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 MariaDB 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 MariaDB Migration.