Re: SQL query question

On Jun 18, 2006, at 8:50 , Kevin Jenkins wrote:

> I have a beginner question. I have a table with a bunch of
> filenames and each of them have a date. Multiple files may have
> the same name. 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

I can think of two ways to do this (and there are probably more): one
using standard SQL and one using PostgreSQL extensions. Here's the
standard SQL way:

SELECT filename, date, revision
FROM table_with_bunch_of_filenames
NATURAL JOIN (
SELECT filename, max(date) as date
FROM table_with_bunch_of_filenames
GROUP BY filename
) AS most_recent_dates;

If you don't need the revision, you can just use the subquery-- the
stuff in the
parentheses after NATURAL JOIN.

And here's the way using DISTINCT ON, which is a PostgreSQL extension.

SELECT DISTINCT ON (filename, date)
filename, date, revision
FROM table_with_bunch_of_filenames
ORDER BY filename, date desc;

Hope this helps.

Michael Glaesemann
grzm seespotcode net