[gameprogrammer] Re: SQL question

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

Other related posts: