sql - Simulating row_number() MySQL -

sql - Simulating row_number() MySQL -

can help me row_number() on partition in mysql?

i've tried script below , doesn't work properly. i'm trying select first product bought. columns selecting client_id, product , purchase_date.

select * from(select * (select @rownum:=@rownum + 1 row_number,t.* (select cliend_id,prod,purchase_date mytable grouping cliend_id,prod,purchase_date order cliend_id,purchase_date) asc) t, (select @rownum := 0) r)a)b;

result:

row_number client id prod purchase date 1 1111 apple 11-nov-10 2 1111 yougurt 11-nov-11 3 1111 candy 11-nov-13 4 2222 chocolate 11-nov-09 5 2222 pear 9-sep-09 6 2222 beer 12-sep-10 7 2222 cheese 12-sep-14 8 1234 apple 15-nov-12 9 1234 candy 4-oct-14

how can result?

row_number client id prod purchase date 1 1111 apple 11-nov-10 2 1111 yougurt 11-nov-11 3 1111 candy 11-nov-13 1 2222 chocolate 11-nov-09 2 2222 pear 9-sep-09 3 2222 beer 12-sep-10 4 2222 cheese 12-sep-14 1 1234 apple 15-nov-12 2 1234 candy 4-oct-14

thanks, rodica

you can "remember" value of cliend_id previous row, , utilize information:

select (@rn := if(@c = cliend_id, @rn + 1, if(@c := @liend_id, 1, 1) ) ) rownum, cliend_id, prod, purchase_date (select cliend_id, prod, purchase_date mytable grouping cliend_id, prod, purchase_date ) t cross bring together (select @rn := 0, @c := 0) vars order cliend_id, purchase_date;

you should assignment of @rn , @c in same statement, because mysql not guarantee order of evaluation of expressions in select.

mysql sql window-functions