SQL query question - Mailing list pgsql-general

Hi!

First I want to say thanks for writing PostgreSQL.  It's nice to have
a free alternative.

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

So the result would be
filename date     revision
file1    10/05/07 2
file2    10/05/08 1

The best I can figure out is how to get the biggest date for a
particular named file:

SELECT * from FileVersionHistory WHERE modificationDate = (SELECT
max(modificationDate) FROM FileVersionHistory WHERE filename='File1');

The best I can accomplish is to run the query once for each file in a
loop in C++ code.  But that's inefficient.  I don't want to name the
files in the query.

I want one query that gives me the final result.

Ideas?

In case you need it, here's the table setup

const char *command =
"BEGIN;"
"CREATE TABLE Applications ("
"applicationKey serial PRIMARY KEY UNIQUE,"
"applicationName text NOT NULL UNIQUE,"
"installPath text NOT NULL,"
"changeSetID integer NOT NULL DEFAULT 0,"
"userName text NOT NULL"
");"
"CREATE TABLE FileVersionHistory ("
"applicationKey integer REFERENCES Applications ON DELETE CASCADE,"
"filename text NOT NULL,"
"content bytea,"
"contentHash bytea,"
"patch bytea,"
"createFile boolean NOT NULL,"
"modificationDate timestamp NOT NULL DEFAULT LOCALTIMESTAMP,"
"lastSentDate timestamp,"
"timesSent integer NOT NULL DEFAULT 0,"
"changeSetID integer NOT NULL,"
"userName text NOT NULL,"
"CONSTRAINT file_has_data CHECK ( createFile=FALSE OR ((content IS NOT
NULL) AND (contentHash IS NOT NULL) AND (patch IS NOT NULL)) )"
");"
"COMMIT;";

Add an application and file

-- Insert application
INSERT INTO Applications (applicationName, installPath, userName)
VALUES ('Game1', 'C:/', 'Kevin Jenkins');

-- Insert file (I would do this multiple times, once per file)
INSERT INTO FileVersionHistory (applicationKey, filename, createFile,
changeSetID, userName)
VALUES (
1,
'File1',
FALSE,
0,
'Kevin Jenkins'
);