Re: SQL Query to get Max (Date)

From: Mark D Powell <mark.powell_at_eds.com>
Date: 7 Feb 2002 07:10:14 -0800
Message-ID: <178d2795.0202070710.684ab157@posting.google.com>

zzz_at_postmaster.co.uk (Mike Haig) wrote in message news:<ea1237b2.0202070335.6194b70_at_posting.google.com>...
> Hello, I need some help in creating a query please. I have the two tables below;
>
>
> Table 1:
>
> NameField
>
>
> Table2
>
> Namefield
> Datefield
> Message
>
> I want to get this result:
>
> Name, DateField (the datefield of the last posted message for each name).
>
> Can anyone help with this query?
>
> Thanks
>
> Tony

In Oracle version 7.3 and up you could write the query as follows

select A.namefield, B.datefield
from table1 A,

       (select C.namefield, max(c.datefield) as datefield
        from   table2 C
        group by c.namefield
       ) B

where A.namefield = B.namefield

Beware of typos as I just typed this on the fly but the technique is known as an inline view (using a select statement for a table in the from clause).

• Mark D Powell --

Received on Thu Feb 07 2002 - 09:10:14 CST