MySQL numbering dates per id -

my question simple answer (sorry that) cannot find solution.

i have table this:

id / date   1 / 2013-5-5 13:44:12   1 / 2013-5-5 15:34:19   1 / 2013-6-5 05:14:07   2 / 2012-3-4 06:33:33   2 / 2013-5-5 12:23:10   3 / 2012-5-7 11:43:17    

what want this:

id / date / position   1 / 2013-5-5 13:44:12 / 1     1 / 2013-5-5 15:34:19 / 2   1 / 2013-6-5 05:14:07 / 3    2 / 2012-3-4 06:33:33 / 1    2 / 2013-5-5 12:23:10 / 2    3 / 2012-5-7 11:43:17 / 1   

so earliest date per id should position 1, second earliest 2 , on. how can create position column in mysql?

thank much!

unfortunately, mysql doesn't have windowing functions assign row number data. there few ways can result, return position number using subquery similar following:

select t.id,   t.date,   (select count(*)    yourtable r    r.id = t.id      , r.date <= t.date) position yourtable t order t.id, t.date; 

see sql fiddle demo.

you implement user defined variables:

select id, date, position (   select t.id,     t.date,     @row:=case              when @prev=t.id , @pd<= t.date             @row else 0 end +1 position,     @prev:=t.id,     @pd:=t.date   yourtable t   cross join (select @row:=0, @prev:=0, @pd:=null) c   order t.id, t.date )d 

see sql fiddle demo