MySQL selects and groups rows if one by one

Question

I am developing an auction application in which bids are stored in a table with the following format:

id | user_id | auction_id | placed_at

I would like to group bids by user ID and select their number, but only if user IDs appear one after another. Bids are sorted by put_at in descending order. Here is what I mean:

1 | 1 | 1 | 01-04-2010 00:00:06
2 | 1 | 1 | 01-04-2010 00:00:05
3 | 2 | 1 | 01-04-2010 00:00:04
4 | 2 | 1 | 01-04-2010 00:00:03
5 | 3 | 1 | 01-04-2010 00:00:02
6 | 2 | 1 | 01-04-2010 00:00:01

In this case, I should get:

count | user_id | auction_id
  2   |    1    |     1
  2   |    2    |     1
  1   |    3    |     1
  1   |    2    |     1

How can I achieve this result? The results will be used to create a stream of auction activity, so they will be used to generate a string, such as "John Smith’s bid at this auction 2 times." The application is built using Ruby on Rails, if that helps.

Thanks in advance.

Answer 1

SELECT  user_id, auction_id, COUNT(*)
FROM    (
        SELECT  @r := @r + CASE WHEN @user_id = user_id AND @auction_id = auction_id THEN 0 ELSE 1 END AS _g,
                user_id, auction_id,
                @user_id := user_id,
                @auction_id := auction_id
        FROM    (
                SELECT  @r := 0,
                        @user_id := NULL,
                        @auction_id := NULL
                ) vars,
                mytable
        ORDER BY
                auction_id DESC, placed_at DESC
        ) q
GROUP BY
        user_id, auction_id, _g
ORDER BY
        _g