This took me a while to figure out, so thought it worth documenting. Here is a simplified example that explains the problem and solution, tested on MySQL 5.0.

An office worker keeps track of who she has contacted. After a while she builds up a table as follows:

Now she needs to see who she contacted the most recently and in what order. In other words, with the above data she wants the following list: sally, harry, pudding, josef.

Her initial immediate reaction is SQL like this:

SELECT DISTINCT contact FROM table ORDER BY id DESC;

This returns the correct data, but in the wrong order: pudding, sally, harry, josef. This is because DISTINCT seems to remember only the first instance as it appears in the table, all the rest are considered duplicates and ignored.

After some effort, the solution turns out to be not to use DISTINCT, but rather GROUP BY and ORDER BY MAX() to invoke magic:

SELECT contact FROM table GROUP BY contact ORDER BY MAX(id) DESC;

Excitingly that returns the correct data in the correct order. We could still use a DISTINCT in there, but it is superfluous and would add unnecessary computation, GROUP BY does the job of DISTINCT.

The example could be further complicated. For example the the table could have a timestamp added (ORDER BY MAX would work on that too), and a user id so that multiple office workers could use it (include a WHERE user_id = x to find only for a particular user).