select max() problem

[Ceud]

I hav a log viewer to show each time a user logs onto my site.
I want to show only the latest entry (most recent timestamp)
for each individual user mentioned in an entry.

The sql I have currently is this:

SELECT `user`,`ip`,`log`,max(timestamp)
FROM `logs` WHERE `type`='login'
GROUP BY `user` LIMIT 0,20


However this seems to actually show the oldest entry per unique user.

Can anyone tell me what is wrong here? I must confess I'm at a loss.

[fenway]

You're missing an ORDER BY yourCol DESC before the LIMIT clause.

[Ceud]

Ok, I tried your tip but it still pulls out exactly the same records (oldest per user, instead of newest), except it now orders the results differently.

If I change some stuff I can manage to get the correct timestamp showing but the ip and log are NOT from the same row as that correct timestamp.

[Wildbug]

These seemed to worked for me on a mockup of your table...

Using aliases:
[code]SELECT t1.user,t1.ip,t1.log,t1.timestamp,t1.type FROM logs AS t1,logs AS t2 WHERE t2.type='login' AND t1.user=t2.user GROUP BY t1.user,t1.timestamp HAVING MAX(t2.timestamp)=t1.timestamp ORDER BY t1.timestamp DESC LIMIT 0,20[/code]
Using subqueries (v4.1+):
[code]SELECT t1.user,t1.ip,t1.log,t1.timestamp FROM logs AS t1,(SELECT user,MAX(timestamp) AS tsmax FROM logs WHERE type='login' GROUP BY user) AS t2 WHERE t1.user=t2.user AND t1.timestamp=t2.tsmax ORDER BY timestamp DESC LIMIT 0,20[/code]

[Ceud]

[!--quoteo(post=384615:date=Jun 16 2006, 04:51 PM:name=Wildbug)--][div class=\'quotetop\']QUOTE(Wildbug @ Jun 16 2006, 04:51 PM) [snapback]384615[/snapback][/div][div class=\'quotemain\'][!--quotec--]
These seemed to worked for me on a mockup of your table...

Using aliases:
[code]SELECT t1.user,t1.ip,t1.log,t1.timestamp,t1.type FROM logs AS t1,logs AS t2 WHERE t2.type='login' AND t1.user=t2.user GROUP BY t1.user,t1.timestamp HAVING MAX(t2.timestamp)=t1.timestamp ORDER BY t1.timestamp DESC LIMIT 0,20[/code]
Using subqueries (v4.1+):
[code]SELECT t1.user,t1.ip,t1.log,t1.timestamp FROM logs AS t1,(SELECT user,MAX(timestamp) AS tsmax FROM logs WHERE type='login' GROUP BY user) AS t2 WHERE t1.user=t2.user AND t1.timestamp=t2.tsmax ORDER BY timestamp DESC LIMIT 0,20[/code]
[/quote]

Well you are right Wildbug. I tried the code using aliases and it works great.

Thanks for your help =]

[fenway]

Good thing Wildbug actually read the post -- I didn't realize you wanted the max within a group.