How to Generate Row Numbers per Group in MySQL?

Row Number per Group in MySQL

In database management systems, understanding row numbering within groups is crucial for data analysis and manipulation. In MySQL, generating row numbers based on specific criteria, such as crew ID and type, can be achieved through various techniques.

Solution

To generate a row number for each unique combination of crew ID and type, you can utilize the following MySQL query:

SELECT    id,
          crew_id,
          amount,
          type,
         ( 
            CASE type 
            WHEN @curType 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curType := type END
          ) + 1 AS rank
FROM      Table1 p,
          (SELECT @curRow := 0, @curType := '') r
ORDER BY  crew_id,type asc;

In this query:

• Table1 represents the original table containing the data.
• @curRow and @curType are user-defined variables initialized to 0 and an empty string, respectively.
• The CASE statement handles row numbering based on type. If the type matches @curType, the row number (rank) is incremented by one. Otherwise, it resets @curRow to 1 and updates @curType to the current type.
• The 1 in the CASE statement ensures that the row number starts from 1.

This query returns the desired output with the row numbers added as an additional column named 'rank'.

The above is the detailed content of How to Generate Row Numbers per Group in MySQL?. For more information, please follow other related articles on the PHP Chinese website!