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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
