How to Generate Row Numbers Within Groups in MySQL?

Row Number within Groups in MySQL

Problem:

Produce a MySQL query to generate a row number for each group of records based on two fields, such as crew_id and type. The row number should reset for each new combination of crew_id and type.

Sample Data:

id   crew_id   amount    type
 1      4       1000      AUB
 2      4       1500      AUB
 3      5       8000      CA
 4      4       1000      CA
 5      5       1000      AUB
 6      6       3000      AUB
 7      4       2000      CA
 8      6       3500      AUB
 9      4       5000      AUB
 10     5       9000      CA
 11     5       1000      CA

Desired Output:

id    crew_id   amount   type    row_number
 1      4       1000      AUB        1    
 2      4       1500      AUB        2 
 9      4       5000      AUB        3
 4      4       1000      CA         1
 7      4       2000      CA         2
 5      5       1000      AUB        1
 3      5       8000      CA         1
 10     5       9000      CA         2
 11     5       1000      CA         3
 6      6       3000      AUB        1
 6      6       3000      AUB        2

Solution:

To achieve the desired row numbers, we can utilize a combination of a subquery and a CASE statement:

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:

• The subquery (SELECT @curRow := 0, @curType := '') r initializes two user-defined variables: @curRow to 0 and @curType to an empty string.

• The CASE statement in the outer SELECT query compares the type column with @curType to determine if the current row belongs to the same group.

  • If type equals @curType, @curRow is incremented by 1.
  • If type differs from @curType, @curRow is reset to 1, and @curType is updated to the current type value.
• The calculated value is then added to 1 to produce the row number.
• The result set is ordered by crew_id and type in ascending order.

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