CODEWITHSUNDEEP
sqldistinct
Josh
Josh

Reputation: 2123

SQL query for select distinct with most recent timestamp first

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

Upvotes: 13

Views: 25517

Answers (2)

bi4nchi
bi4nchi

Reputation: 539

This answer won't actually guarantee the other columns to be of the same record as the username / timestamp, but the following will (using a subquery).

select t1.*
from (
  select ForeignKeyId,AttributeName, max(Created) AS MaxCreated
  from  YourTable
  group by ForeignKeyId,AttributeName
) t2
join YourTable t1
on t2.ForeignKeyId = t1.ForeignKeyId
and t2.AttributeName = t1.AttributeName
and t2.MaxCreated = t1.Created

Answer found at:

Select distinct most recent

Upvotes: 2

Vikram
Vikram

Reputation: 8333

try the following query with table1 as your tablename:

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

Upvotes: 26

Related Questions