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'
);
