mysql - Distinct order-number sequence for every customer -

i have table of orders. each customer (identified email field) has own orders. need give different sequence of order numbers each customer. here example:

---------------------------- |      email      | number | ---------------------------- |  test@com.com   |    1   | ---------------------------- | example@com.com |    1   | ---------------------------- |  test@com.com   |    2   | ---------------------------- |  test@com.com   |    3   | ---------------------------- |  client@aaa.com |    1   | ---------------------------- | example@com.com |    2   | ---------------------------- 

is possible in simple way mysql?

if want update data in table after insert, first of need primary key, simple auto-increment column job. after can try elaborate various script fill number column, can see other answer, not "simple way".

i suggest assign order number in insert statement, obtaining order number "simpler" query.

select coalesce(max(`number`), 0)+1  orders  email='test1@test.com' 

if want in single insert (better performance , avoid concurrency problems)

insert orders (email, `number`, other_field) select email, coalesce(max(`number`), 0) + 1 number, 'note...' other_field orders email = 'test1@test.com'; 

to more confident not assign @ same customer 2 orders same number, suggest add unique constraint columns (email,number)