In a previous post, we discussed how to generate a row number for each row in MySQL using a variable. However, MySQL does not have a built-in function like SQL Server’s ROW_NUMBER() to generate row numbers. In this post, we will explore an alternate method to reset the row number for each group, similar to SQL Server’s ROW_NUMBER() OVER (PARTITION BY) method.

Let’s consider the following data:

CREATE TABLE mysql_testing (
    db_names VARCHAR(100)
);

INSERT INTO mysql_testing
SELECT 'SQL Server'
UNION ALL SELECT 'MySQL'
UNION ALL SELECT 'SQL Server'
UNION ALL SELECT 'MongoDB'
UNION ALL SELECT 'SQL Server'
UNION ALL SELECT 'MySQL'
UNION ALL SELECT 'Oracle'
UNION ALL SELECT 'PostGreSQL'
UNION ALL SELECT 'MongoDB'
UNION ALL SELECT 'SQL Server'
UNION ALL SELECT 'SQL Server'
UNION ALL SELECT 'MongoDB'
UNION ALL SELECT 'MySQL'
UNION ALL SELECT 'Oracle'
UNION ALL SELECT 'MongoDB'
UNION ALL SELECT 'PostGreSQL';

To generate the row number and reset it for each group of db_names, we can use two variables. One variable will increment the numbers, while the other will be used to reset the number to 1 whenever the group value changes.

Method 1: Using a SELECT Statement

In this method, we set a variable and use it in a SELECT statement. We reset the variable whenever the group value changes.

SET @row_number := 0;
SET @db_names := '';

SELECT
    @row_number := CASE WHEN @db_names = db_names THEN @row_number + 1 ELSE 1 END AS row_number,
    @db_names := db_names AS db_names
FROM
    mysql_testing
ORDER BY
    db_names;

Method 2: Using a Variable as a Table

In this method, we use a variable as a table and cross join it with the source table. We reset the variable whenever the group value changes.

SELECT
    @row_number := CASE WHEN @db_names = db_names THEN @row_number + 1 ELSE 1 END AS row_number,
    @db_names := db_names AS db_names
FROM
    mysql_testing,
    (SELECT @row_number := 0, @db_names := '') AS t
ORDER BY
    db_names;

Both methods will produce the following result:

row_number   db_names
1            MongoDB
2            MongoDB
3            MongoDB
4            MongoDB
1            MySQL
2            MySQL
3            MySQL
1            Oracle
2            Oracle
1            PostGreSQL
2            PostGreSQL
1            SQL Server
2            SQL Server
3            SQL Server
4            SQL Server
5            SQL Server

By using these methods, you can easily reset the row number for each group in SQL Server, even in the absence of the ROW_NUMBER() OVER (PARTITION BY) function in MySQL.