How to Number Rows by Group in MySQL?

Numbering Rows by Group in MySQL

Consider the following table structure:

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

To generate a result set that includes a row number for each combination of crew_id and type, execute the following 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;

Explanation:

• The query first initializes two user variables, @curRow and @curType, to track the current row number and the current value of type, respectively.
• In the main query, the CASE statement checks if the current value of type matches the previously stored value in @curType. If they match, it increments @curRow by 1. Otherwise, it resets @curRow to 1 and updates @curType with the new value.
• The expression (@curRow := @curRow 1) 1 adds the incremented or reset value of @curRow by 1 to generate the row number.
• The query is ordered by crew_id and type to properly group and number the rows.

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

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