Row number per group in mysql

Question

I want to produce query result base on this scenario that can create row number according to crew_id and type.

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 

OUTPUT must be the ff:

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 

I want a single select statement only in this output

Answer 1

Please go through my fiddle

This One Last Tried

    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; 

Answer 2

The question is quite old. But I would like to post it in case someone will have a same problem.

First of all, described answers do not work correct. For example, for

id   crew_id   amount    type 1      4       1000      AUB 2      4       1500      AUB 5      5       1000      AUB 6      6       3000      AUB 8      6       3500      AUB 9      4       5000      AUB 

(I just removed rows with type 'CA') the result table will be

id   crew_id   amount    rank   type 1      4       1000      1      AUB 2      4       1500      2      AUB 9      4       5000      3      AUB 5      5       1000      4      AUB 6      6       3000      5      AUB 8      6       3500      6      AUB 

So in fact it doesn't use both crew_id and type, it just uses type.

Here is how I solved this problem (probably there is a more elegant way to do it than use two nested 'CASE's, but you get the idea):

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

The main idea remain. I just added a variable @curCrewId. If someone need to use 3 variables for grouping, so just use 3 variables and 3 nested 'CASE's. :)