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