Hi,
try this :
mysql> select * from users;
+--------+----------+-------------+
| userID | username | realname |
+--------+----------+-------------+
| 1 | toto1 | toto1 toto1 |
| 2 | toto2 | toto2 toto2 |
| 3 | toto3 | toto3 toto3 |
| 4 | toto4 | toto4 toto4 |
+--------+----------+-------------+
4 rows in set (0.00 sec)
mysql> select * from logins;
+------+---------------------+
| ID | lastLogin |
+------+---------------------+
| 1 | 2005-01-01 00:10:00 |
| 1 | 2005-06-01 12:00:00 |
| 2 | 2005-01-25 00:00:00 |
| 2 | 2005-05-24 01:00:00 |
| 3 | 2005-01-15 10:10:00 |
| 3 | 2005-06-25 14:00:00 |
+------+---------------------+
6 rows in set (0.00 sec)
mysql> select userid, username, realname, max(lastLogin) lastLogin
-> from users left join logins on userid = logins.id
-> group by username
-> order by lastLogin DESC;
+--------+----------+-------------+---------------------+
| userid | username | realname | lastLogin |
+--------+----------+-------------+---------------------+
| 3 | toto3 | toto3 toto3 | 2005-06-25 14:00:00 |
| 1 | toto1 | toto1 toto1 | 2005-06-01 12:00:00 |
| 2 | toto2 | toto2 toto2 | 2005-05-24 01:00:00 |
| 4 | toto4 | toto4 toto4 | NULL |
+--------+----------+-------------+---------------------+
4 rows in set (0.00 sec)
Mathias
Selon [EMAIL PROTECTED]:
>
> 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
>
>
> --
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
