[gameprogrammer] SQL question

I have a table with a list of files and dates. Each file may have multiple dates. So I want to get a list of files with the greatest date for each file.

I got this far:

-- Find row of a particular file with greatest date for that file.
SELECT * from FileVersionHistory WHERE modificationDate = (SELECT max(modificationDate) FROM FileVersionHistory WHERE filename='File1');

If I were to call that a bunch of times, replacing File1 with the name of each unique file in the table, then it would work. I can't figure out how to do that automatically though.

Here is the table:

CREATE TABLE Applications (
applicationKey serial PRIMARY KEY UNIQUE,
applicationName text NOT NULL UNIQUE,
installPath text NOT NULL,
changeSetID integer NOT NULL DEFAULT 0, -- User should increment once for every commit of changes on the database
userName text NOT NULL -- Who created the application
);

CREATE TABLE FileVersionHistory (
applicationKey integer REFERENCES Applications ON DELETE CASCADE, -- Which application this file is used for
filename text NOT NULL, -- Relative path to the file + filename from Applications::installPath.
content bytea, -- Contents of this file (only used if createFile==true)
contentHash bytea, -- 20 byte hash of content (only used if createFile==true)
patch bytea, -- Patch to get to the current version (only used if createFile==true && if newer versions of this file exist which also have createFile==true)
createFile boolean NOT NULL, -- True to create or patch this file. False to delete it.
modificationDate timestamp NOT NULL DEFAULT LOCALTIMESTAMP, -- When this change occured
lastSentDate timestamp, -- last time this change was sent
timesSent integer NOT NULL DEFAULT 0, -- How many times this change was sent
changeSetID integer NOT NULL, -- The change set number for when this operation took place
userName text NOT NULL, -- Who performed this operation
CONSTRAINT file_has_data CHECK ( createFile=FALSE OR ((content IS NOT NULL) AND (contentHash IS NOT NULL) AND (patch IS NOT NULL)) )
);

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

-- Insert file
INSERT INTO FileVersionHistory (applicationKey, filename, createFile, changeSetID, userName)
VALUES (
1,
'File1',
FALSE,
0,
'Kevin Jenkins'
);


---------------------
To unsubscribe go to http://gameprogrammer.com/mailinglist.html


Other related posts: