<[EMAIL PROTECTED]> wrote on 06/01/2005 11:49:35 AM:
>
> I have two tables, cutting out the extra stuff they boil down to:
>
> users:
> userID int,
> username varchar(11),
> realname varchar(40)
>
> logins:
> ID int,
> lastLogin timestamp
>
> So, what I am doing is:
> select user.id, username, realname, lastLogin
> from users left join logins on users.id = logins.id
> group by username
> order by lastLogin DESC
>
> What I want is all the users, no matter if they have logged in or
> not. That is what the left join does. But, if they have logged in,
> I want the last login date. Right now I get the first login date.
> Changing DESC to ASC only changes the display order of the return
> set. I have added DESC and ASC to the group by, but that doesn't work
at all.
>
> Advice?
>
> --ja
>
>
> --
>
For dates, "latest" = "greatest value" or MAX().
select user.id, username, realname, max(lastLogin) as lastLogin
from users
left join logins
on users.id = logins.id
group by user.id, username, realname;
YOU MUST include all of your non-aggregated column in your group by
statement. MySQL has a "forgiveness" built into this rule but other RDBMS
systems would have rejected your query based on that very fact. What MySQL
does is select any random record that falls into the GROUP BY condition
and give you the values from it. In this case it was probably the first
record in the logins table that matched each user. This is a well-known
MySQL "gotcha" and it got you, too.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
