SQL query for select distinct with most recent timestamp first

Question

I have a mysql table with three columns: username, location, timestamp. This is basically a log of user activity of what location they are in and the time that they were there.

What I want to do is select a distinct username+location where only the most recent item (by timestamp) is provided.

So say the table consists of:

tom roomone 2011-3-25 10:45:00
tom roomtwo 2011-3-25 09:00:00
tom roomtwo 2011-3-25 08:30:00
pam roomone 3011-3-25 07:20:23

I would want only these to be selected:

tom roomone 2011-3-25 10:45:00
tom roomtwo 2011-3-25 09:00:00

Answer 1

try the following query with table1 as your tablename:

select username, location, max(timestamp) from table1
group by  username, location