Jump to content

select max() problem


Ceud

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.
Link to comment
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.
Link to comment
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]
Link to comment
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 =]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.