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@xxxxxxxx> wrote:
On Sat, Jun 17, 2006 at 16:50:59 -0700,
Kevin Jenkins <gameprogrammer@xxxxxxxxxx> 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
