[gameprogrammer] Re: SQL question

But that only gives you one file, and you have to specify which filename you want.

Using group by in the query that I suggested should give you a recordset of all the files and the latest modified date per each file.

I don't see any easier way to do it. There are plenty of harder ways though. Subqueries, stored procedures, or writing a script that first selects out all the file names, and runs the query you suggest on each one to get the latest modified date.


On Sat, 17 Jun 2006 23:22:12 -0500, Vince <uberneen@xxxxxxxxx> wrote:

I'm with Mike on this one.

SELECT * from FileVersionHistory WHERE
filename='File1' ORDER BY modificationDate DESC LIMIT
1;


--- brian <brianevans@xxxxxxxxxxxxxxx> wrote:

Perhaps you could use group by?

select filename, MAX(modificationDate) from
FileVersionHistory group by
filename order by filename asc

If that syntax doesn't work for you, something
similar should.  :P



On Sat, 17 Jun 2006 22:17:28 -0500, Mike Gillissie
<Niyoto@xxxxxxxxxx>
wrote:

> I'm a little rusty on this, but can't you do an
ORDER BY to sort by the
> modification date, then either add a "LIMIT 1" if
your db supports it,
> or just use the first element of the record set?
>
> ----- Original Message ----- From: "Kevin Jenkins"

> <gameprogrammer@xxxxxxxxxx>
> To: <gameprogrammer@xxxxxxxxxxxxx>
> Sent: Saturday, June 17, 2006 7:20 PM
> Subject: [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
>>
>>
>>
>
>
>
>
> ---------------------
> To unsubscribe go to
http://gameprogrammer.com/mailinglist.html
>
>



--
Using Opera's revolutionary e-mail client:
http://www.opera.com/mail/


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





__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


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





--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/


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


Other related posts: