Row_number() in MySQL

Emulating ROW_NUMBER() in previous MySQL versions

If you're teamed with a predates-MySQL 8.0 version, don't worry. We can fake ROW_NUMBER() with user-defined variables. Check this out:

SET @row_number = 0;
SELECT 
  (@row_number:=@row_number + 1) AS row_num, t.*
FROM 
  (SELECT * FROM your_table ORDER BY id) AS t;


This acts as "ROW_NUMBER's stunt double." We increment a variable for every row, masquerading as ranking.

Partitioning dzata

Sequencing over partitions? You're speaking my language! Reset that awesome variable when a new partition column value pipes up:

SET @row_number = 0;
SELECT *, 
  (@row_number:=CASE WHEN @prev_value = partition_column THEN @row_number + 1 ELSE 1 END) AS `row_num`,
  @prev_value := partition_column
FROM your_table,
  (SELECT @prev_value:=NULL) AS init
ORDER BY 
  partition_column, order_criteria;


Workarounds for older MySQL versions

Before MySQL 8.0, we programmers had to play MacGyver, applying everything from self-joins to variables. Below are some ingenious methods that deserve a hat tip.

Self-Join for maximum impact

Need to high-five the row with the highest value for each group? Self-join to the rescue:

SELECT a.*
FROM your_table a
LEFT JOIN your_table b ON a.group_column = b.group_column AND a.rank_criteria < b.rank_criteria
WHERE b.rank_criteria IS NULL;


This maneuver relies on the fact that there's no 'superior' row (same group, higher rank).

Groupwise maximum without lifeboats

Groupwise maximum is a proverbial "SQL head-scratcher." Here's how we can crack it without analytic functions:

SELECT a.*
FROM your_table a
WHERE 
  a.rank_criteria = (
    SELECT MAX(b.rank_criteria)
    FROM your_table b
    WHERE a.group_column = b.group_column
  );


Pivoting on user-defined variables

For custom rankings schemes, variables are your best friends. Here's a dandy to partition and order rows:

SELECT
  name,
  IF(@prev != category, @rn:=1, @rn:=@rn+1) as rank, /* A SQL Yo-Yo! */
  @prev:=category as category
FROM
  items,
  (SELECT @rn:=0, @prev:=NULL) as vars
ORDER BY
  category,
  value DESC;


Our variable @rn resets with each new category (by incrementing or reverting to 1), and @prev traces the last category.

A word of wisdom and potential trips

• Putting things in order matters. Unordered data can mix up your ranking.
• MySQL variables aren’t known for session safety. Beware of conflicting queries!
• Test, test and re-test. Corner cases like duplicate values, null values, or surprising data types can trip your logic.