-
SQL Question!
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!
-
Code:
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
-
I haven't use the Group function before.
Thanks!
Best Regards!
Owen
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|