Jump to content

Archived

This topic is now archived and is closed to further replies.

Ceud

select max() problem

Recommended Posts

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.

Share this post


Link to post
Share on other sites
You're missing an ORDER BY yourCol DESC before the LIMIT clause.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[!--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 =]

Share this post


Link to post
Share on other sites
Good thing Wildbug actually read the post -- I didn't realize you wanted the max within a group.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.