Find records that have the most current date??

Hi

I need some help again. I have two tables

table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004

table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|

table_1 contain records on employee and the supervisor they are under at a certain date.
As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.
And from table_2, i'll like to extract the employee's name.
These records extracted from both the tables would the be put into a new table,table_3

Example:
For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004

table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004

Confusing???
How to write an SQL statement to perform this??
Course work on Oracle is really killing me ...hope someone can help me out.
Thanks

regard,
YuLing

CREATE TABLE table3 AS SELECT
a.emp_id,b.emp_name,a.supp_id,a.supp_name,a.date
FROM table1 a, table2 b
WHERE (a.emp_id,a.date) = (SELECT emp_id, max(date) from table1 GROUP BY emp_id)
AND a.emp_id = b.emp_id

i try out the sql but i got this error msg

WHERE (a.emp_id,a.date) = (SELECT emp_id, max(date) from table1 GROUP BY emp_id)
*
ERROR at line 5:
ORA-01427: single-row subquery returns more than one row

then on further checking i realize that some of the record in table_1 have the same date for their max date.

Emp_no|Date
-------------------------
00001 |01-March-2004
00001 |01-APRIL-2004
00001 |01-APRIL-2004

If it is this case,can i still know which date is the most currently updated?

Thanks

regards,
YuLing

If the most current date cant be know when both record have the same date, then is it possible to extract out both of the record that contain the most recent date??

Example

Emp_no|Emp_name|Sup_no|Sup_name|Date
-00001-|--ABCD--|-0005-|--QWER--|01-APRIL-2004
-00001-|--ABCD--|-0006-|--ASDF --|01-APRIL-2004
.
.
.

Thanks

Just make the following modification in your where clause.

WHERE (a.emp_id,a.date) IN (SELECT emp_id, max(date) from table1 GROUP BY emp_id)

Thanks for all ur help shafique

but so sorry to bother u again, cos there is one more question i would like to ask...

Is it possible to only extract the last row from the many rows of the latest date??

Example:

table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00001--|--00006 --|---BCDE--|01-APRIL-2004
--00001--|--00007 --|---CDEF--|01-APRIL-2004
--00001--|--00008 --|---DEFG--|01-APRIL-2004

table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|


From the table_1 and table_2, produce the following table_3
table_3
Emp_id |Emp_name| Sup_id |Sup_name|Date
-00001-|--QWER--|-00008-|--DEFG--|01-APRIL-2004

Thank you.