Jump to content


Photo

select max() problem


  • Please log in to reply
5 replies to this topic

#1 Ceud

Ceud
  • New Members
  • Pip
  • Newbie
  • 3 posts
  • LocationScotland, UK

Posted 15 June 2006 - 09:15 PM

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.
Ceud
[[a href="http://www.future-online.co.uk" target="_blank"]FO[/a]]

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 June 2006 - 07:34 AM

You're missing an ORDER BY yourCol DESC before the LIMIT clause.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Ceud

Ceud
  • New Members
  • Pip
  • Newbie
  • 3 posts
  • LocationScotland, UK

Posted 16 June 2006 - 10:48 AM

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.
Ceud
[[a href="http://www.future-online.co.uk" target="_blank"]FO[/a]]

#4 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 16 June 2006 - 03:51 PM

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

Using aliases:
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
Using subqueries (v4.1+):
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

Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#5 Ceud

Ceud
  • New Members
  • Pip
  • Newbie
  • 3 posts
  • LocationScotland, UK

Posted 16 June 2006 - 07:24 PM

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

Using aliases:
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
Using subqueries (v4.1+):
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
[/quote]

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

Thanks for your help =]
Ceud
[[a href="http://www.future-online.co.uk" target="_blank"]FO[/a]]

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 June 2006 - 08:37 PM

Good thing Wildbug actually read the post -- I didn't realize you wanted the max within a group.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users