SQL Question!

[owen_yuen]

User table
USERID......Name.......STATUS
1.............Owen........active
2.............Tom.........active
3.............Jimmy.......active
4.............Eason.......Inactive


Log table
ID........USERID......LastAccess
1.........1.............10/21/2003
2.........1.............10/22/2003
3.........1.............10/23/2003
4.........2.............09/21/2003
5.........2.............09/25/2003
6.........3.............10/21/2003
7.........4.............10/21/2003

How can I form a SQL to pull out all the ACTIVE user from user table, plus the user lastest lastaccess date.

Report will looke like
1.............Owen........active........10/23/2003
2.............Tom.........active........09/25/2003
3.............Jimmy.......active........10/21/2003

SELECT u.userid, u.name, u.status, l.lastaccess
FROM U.user, L.log
Where u.status = 'active' and .....???????

Thanks for advice!

[stecal]

SQL> select * from user1;

        ID NAME                 STATUS
---------- -------------------- ---------------
         1 Owen                 active
         2 Tom                  active
         3 Jimmy                active
         4 Eason                inactive

SQL> select * from log1;

        ID     USERID L_ACCESS
---------- ---------- ---------
         1          1 21-OCT-03
         2          1 22-OCT-03
         3          1 23-OCT-03
         4          2 21-SEP-03
         5          2 25-SEP-03
         6          3 21-OCT-03
         7          4 21-OCT-03

7 rows selected.

SQL> select userid, name, status, max(l_access)
  2  from user1, log1
  3  where user1.id = userid
  4  and status = 'active'
  5  group by userid, name, status;

    USERID NAME                 STATUS          MAX(L_ACC
---------- -------------------- --------------- ---------
         1 Owen                 active          23-OCT-03
         2 Tom                  active          25-SEP-03
         3 Jimmy                active          21-OCT-03

[owen_yuen]

I haven't use the Group function before.

Thanks!

Best Regards!
Owen