[gameprogrammer] Re: SQL question

Agreed - I wasn't paying attention to the "I need the latest version of each
file" requirement...

----- Original Message ----- 
From: "brian" <brianevans@xxxxxxxxxxxxxxx>
To: <gameprogrammer@xxxxxxxxxxxxx>
Sent: Sunday, June 18, 2006 11:20 PM
Subject: [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




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

Other related posts: