MySQL simulated row number

Question

I have read with interest the following blog post about simulating rownum in MySQL:

http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/

I want to extend this concept my having the row number reset on a certain condition being met, i.e. some other column changes value, so the result will look like this:

  id | date       | rownum
-----+------------+--------
   1 | 2013-03-20 |      1
   2 | 2013-03-20 |      2
   3 | 2013-03-20 |      3
   4 | 2013-03-21 |      1   <-- rownum resets because date changes
   5 | 2013-03-21 |      2
   6 | 2013-03-21 |      3
   7 | 2013-03-22 |      1
   8 | 2013-03-22 |      2

I've been trying to figure this out for hours now, without anything satisfactory to show for it. Any help would be appreciated!

in oracle this would be a LEAD or LAG function. that might help your search. — Randy, Mar 29 '13 at 19:16
http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select Similar? — hjpotter92, Mar 29 '13 at 19:18

score 1 · Answer 1 · answered Mar 29 '13 at 19:23

1

You can implement user-defined variables to get the row number for each group of dates using the following:

select id, date, rownum
from
(
  select id,
    date,
    @row:=case when @prev=date then @row else 0 end +1 rownum,
    @prev:=date  
  from yourtable
  cross join (select @row:=0, @prev:=null) c
  order by date
) d
order by date, rownum

See SQL Fiddle with Demo

answered Mar 29 '13 at 19:23

Taryn

242,637
56
362
405

I don't think that's good because you are setting a variable and using it in the same statement. See link in the question. – koriander Mar 29 '13 at 19:54

score 0 · Answer 2 · answered Mar 29 '13 at 19:53

This works, see http://www.sqlfiddle.com/#!2/e7f5e/11

SELECT if(changed, @rownum:=1, @rownum:=@rownum + 1) as rownum, rowdate
FROM (SELECT if(@lastdate=rowdate, '', @lastdate:=rowdate) as changed, rowdate
      FROM dates) flagged,
     (SELECT @rownum:= 0, @lastdate:='') setup;

MySQL simulated row number

2 Answers2