MySQL increment user variable when value changes

Question

I have a table consisting of groups of, for example, five rows each. Each row in each group possesses a date value unique to that group.

What I want to do in my query, is go through the table, and increment a user variable (@count) when this date value changes. That's to say, @count should equal the group count, rather than the row count.

My current query looks like this, in case you're wondering:

SELECT @row := @row +1 AS rownum, date
FROM ( SELECT @row := 0 ) r, stats

Thanks a lot.

Answer 1

What about something like this?

SELECT 
    (CASE WHEN @date <> date THEN @row := @row +1 ELSE @row END) AS rownum, 
    @date:= date, 
    date
FROM ( SELECT @row := 0, @date := NOW() ) r, stats