GROUP BY and ORDER By with MySQL

A few weeks ago, I have some good (and at the same time bitter) experience with MySQL that I would like to share.

Requirement: given the auction and bid tables, select user's latest bid for each auction that she joined

Lets look into two auctions of my sample user whose id is 25

As the target is to get the latest bid for each auction, below is my desired result

And here is the result of my first attempt:

Oops, something is wrong here (of course, otherwise why on earth am I writing this). The second row should have been the bid made in second 47 not 44. The reason for this error is that GROUP BY is performed before ORDER BY, in other words the ORDER BY doesnt have any effect here.

After googling for a while, I am pretty sure that there is no way to tell MySQL order first, group later. You will have to do the grouping and sorting manually. Below is my query:

That's a long and quite complicated query, however the result set is correct

A few days later a colleague looked at my code, saying that it was bad (definitely) and offered some help with this

However, that... is wrong. Although the processing time is reduced dramatically, this solution provides a wrong result set. You might be deceived if only concentrate on the datetime column. The id column is actually wrong, due to aggregation effect. But the query was successfully trigger a debate among us and finally illuminate a right solution

SHAME ON ME! Realizing that how ugly and ineffective my query is, is really painful. Anyway that is a valuable experience with group by and order by, who knows if later on it turns useful.