Hello,
I'm developing an auctions application in which bids are held in a table with the following format:
id | user_id | auction_id | placed_at
I'd like to group the bids by user id and select their counts, but only if the user ids appear one after another. The bids are ordered by placed_at descending. Here's 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 generate an auction's activity stream, so they will be used to generate string such as 'John Smith bid on this auction 2 times.' The application is built with Ruby on Rails, if it helps.
Thank you in advance.