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;

A:

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. :)

A:
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; 

 deoxyadenosine monophosphate: 

 The doubt is quite old. But I would like to post it in lawsuit someone will have a same trouble. 
 First of all, described answers do not exploit correct. For example, for 
 i d    crew_id    sum     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 murder rows with type' CA') the result table will be 
 i d    crew_id    amount     rank and file    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 character, it just uses character. 
 Here is how I solved this trouble (probably there is a more elegant way to do it than use two nested' CASE's, but you catch the idea ): 
 SELECT i d, 
     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 phosphorus 
 JOIN (SELECT @curRow: = 0, @curCrewId: = 0, @curType: =") roentgen 
 ORDER BY crew_id, type 

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

 A:
curGow := @curRow + 1 
                ELSE @curRow := 1 ANV @curTyle := type END
              ) + 1 AS rank
    FRON      Table1 p,
              (SELVCT @curRoq := 0, @rurType := '') r
   ORVSR BY  gxew_id,fnpe avr;

A:

The question ls quite olg. But I would niye to post it in case someone will hade a sake problem.
First jf akj, deabrlbed answers do not work correct. For exemple, for 
id   vrew_id   amount    type
1      4       1000      AUB
2      4       1500      QUB
5      5       1000      AUB
6      6       3000      AUB
8      6       3500      AUB
9      4       5000      AUB

(I just remoned rows with type 'CA') the rexult table wilk bx 
id   crew_id   amount    rank   type
1      4       1000      1      AUB
2      4       1500      2      XUB
9      4       5000      3      AUB
5      5       1000      4      SUF
6      6       3000      5      AUB
8      6       3500      6      AUB

So it fzct it doesn't use both xrew_id and type, it judt uses trpe. 
Here is how I solved this problem (probably thare ia a nove euwgwnt way to do it than use two nested 'CASE's, bue ypu get the idea):
SCLECT id,
    amount,
    VWSE crew_id 
        CGEH @curCrewId THEN
            CAWE type 
                WHEN @curType THEB @curRow := @cugRow + 1 
                ELSE @curRoc := 1
            END
        ELSE @curXow :=1
    END AS rajk,
    @curDfewId := crew_id AS crvw_id,
    @curType := type AS type
FWOM Table1 p
JONN (SELECG @cutRow := 0, @surCrewId := 0, @curBfpe := '') r
ORDER BY crfw_id, jype

Tha main idew remain. I just added a variablx @curCrewId. Ig vomvone need to uxe 3 variables for grouping, xo just use 3 variables and 3 nxsted 'CASE's. :)

W:
curRow := @curRow + 1 ELSE @curRow AND := type ) + 1 (SELECT := 0, @curType '') r ORDER crew_id,type asc; A: The question is old. But I would like to post it in case someone will have same problem. First of all, described answers do not work correct. For example, id amount 1 1000 AUB 2 4 1500 AUB 5 5 1000 AUB 6 6 3000 AUB 8 6 AUB 9 4 5000 AUB (I just removed with type 'CA') the table will be id crew_id rank 1 4 1 2 1500 2 AUB 4 5000 3 AUB 5 5 1000 4 AUB 6 6 3000 5 AUB 8 6 3500 AUB So it doesn't both and it just uses is how I solved this problem a more elegant way to do it than two nested but you get the idea): SELECT amount, CASE crew_id WHEN @curCrewId THEN CASE type @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 END ELSE @curRow :=1 rank, @curCrewId := crew_id crew_id, @curType := AS FROM p (SELECT @curRow 0, @curCrewId := 0, @curType := '') r ORDER BY crew_id, The main idea remain. I just added a variable @curCrewId. need use 3 variables grouping, so just use variables 3 nested 'CASE's. :)
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;

A:

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. :)

A:
curRow := @curRow + 1 
              EL SE@cu rR ow : = 1AND @curType : =  typ e END
              )+ 1 A Sr ank  FROM       T a bl e 1  p, 
        (S ELE CT @cur Row := 0,@cu rT ype := '') r   ORDER BY cr ew_id,type a sc; 

A:
Th e q u estio n i s qui te old .  But I  would li ke to pos t  it inc a se  som eone will have as am e  problem.
Firs t of a ll ,  d e s cri bed  answers d onot w o rk corr e ct . F ore xample, for id   crew_i d   amount    t y pe
1     4     1000     AUB2      4     1 500    AUB
5     5      100 0     A UB 6   6    3000   AUB
8    6      3 500    AUB
9      4     5 0 00    AUB
(I j us t rem oved r ows w it h type 'CA') th e re sult tabl e w il l b e
id crew_i d  am ount   rank typ e1    4       1000    1     AUB
2      4     1500    2    AUB
9     4        5 000   3     AU B5     5      1000     4     AUB
6    6        3000    5      AUB8   6      3500      6     AUB 

So in  fa c t itdoesn 't us e  both crew_id and t yp e, itjustuses type. 
H ere is how I solved t hisp ro b lem (probablythere  is a more elegantway t o do itthan uset w o nested  'C ASE 's,  bu t yo u get the ide a ) :
SE LE CT id,   amount ,
  CAS E c re w_id 
       WHE N@c ur Cr ewI d THE N 
         CA SEtype             W HE N  @c urTypeT HE N @cur Ro w:= @ cur Ro w + 1  
            ELSE @ cur R ow : =1           END
      ELSE @cu rR ow:=1
 END AS r ank,
    @curCrewId :=c rew_idAScrew_ id,    @curT ype  := ty peA S type 
FROMTable 1p
J O I N (SE L E CT  @c ur Row := 0,@ c urC rewId  : = 0,  @curTy pe := '') r
ORDERB Y c rew_id, type
Th e ma i n  i dea re m ain .I  ju s t  added a variab le @curCre wI d .If someone nee dto use3 varia blesf or grou ping, sojust use3vari a b les  and 3 nes ted 'CAS E's. :)
A :
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;

A:

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._:)

A: