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(at)wolff(dot)to> wrote:
> On Sat, Jun 17, 2006 at 16:50:59 -0700,
> Kevin Jenkins <gameprogrammer(at)rakkar(dot)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
>