SQL query help needed 1

[jsteiner87]

I have two tables like what I have below.

NameID Name
-------------------
1 Joe
2 Steve
3 Marty
4 Doug
-------------------


ID NameID Last LogIn
-------------------------
1 1 2006-01-01
2 1 2006-01-02
3 2 2006-01-02
4 3 2006-01-03
5 4 2006-01-03
6 4 2006-01-04
7 1 2006-01-04
8 2 2006-01-04
-------------------------


I have joined the tables (on name ID) and now I want to add to the sql statement so that it lists each of the peoples last login and only the last login. I have tried to use GROUP BY but it only gives me the first login and not the last login.

I need the output to look like the following

Joe 2006-01-04
Steve 2006-01-04
Marty 2006-01-03
Doug 2006-01-04

 

[jpadie]

it's a bit late where i am so i might be overcomplicating things... but i think that you would use a sub-select for this.

SELECT 
   names.name , 
   (
     SELECT 
        max( logons.lastlogontime )
     FROM 
        logons 
     WHERE 
        names.nameid = logons.nameid
   ) AS lastlogon
FROM 
   names
GROUP BY 
   names.nameid

 

[jpadie]

and without the sub-select (told you it was late...)

SELECT logons.nameid, names.name, max( `logons`.`lastlogontime` ) AS lastlogon
FROM logons
INNER JOIN NAMES ON names.nameid = logons.nameid
GROUP BY logons.nameid

 

[jsteiner87]

Thanks jpadie, I used max() and it worked like a charm. I tried to use it before but I must have been doing something wrong.