How to join T1 to T2 and get only the latest record from T2

I need to join two tables. Table 2 (T2) has a many to one relationship with table 1 (T1).

With a simple join like: select T1.*, T2.lastdate, T2.location from T1, T2 where T1.key = T2.key it returns multiples rows because of the duplicates in T2. I need to select only one from T2 based on max(T2.Lastdate). This way, the record from T2 is the latest.

I figured that I could do something like:

select T1.*, T2.lastdate, T2.location
from T1, ( select T2.key, max(t2.lastdate), max(t2.location)

        from T2
        group by t2.key
        having T2.key = T1.key ) T2

But Oracle complains that T1.key is not valid inside the subquery.

Would any help me figure out how to join this tables?

Saludos,

Orlando M. Amador
Cim Programmer
oamador_at_psgapr.jnj.com Received on Mon Mar 05 2001 - 23:26:02 CET