Re: SQL query question

    Another way is to use correlated-subqueries (refrencing outer
query's columns inside a subquery; hope this feature is supported):

select  *
from    FileVersionHistory H1
where   modificationDate = ( select  max(modificationDate)
                             from    FileVersionHistory H2
                             where   H2.filename = H1.filename
                           );

    And if you suspect that some different versions of a file might
have same Date, then you should add DISTINCT to 'select *', else
you'll get duplicates in the result.

Regards,
Gurjeet.


On 6/18/06, Bruno Wolff III <bruno@wolff.to> wrote:
> On Sat, Jun 17, 2006 at 16:50:59 -0700,
>   Kevin Jenkins <gameprogrammer@rakkar.org> wrote:
> >  For example
> >
> > filename date     revision
> > file1    10/05/06 1
> > file1    10/05/07 2
> > file2    10/05/08 1
> >
> > I want to do a query that will return the greatest date for each
> > unique filename
>
> If the revisions for a filename are guarenteed to be ordered by date, then
> another alternative for you would be:
>
> SELECT filename, max(modificationDate), max(revision)
>   FROM FileVersionHistory
>   GROUP BY filename
> ;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>