On Fri, Dec 19, 2008 at 12:39 PM, Sean DiZazzo <[email protected]> wrote: > > oops...record 3 filename should be foo.txt (matching the first > record) The filenames for records 1& 3 match, so the query should > only return records 1 & 2... 1 is more recent date than 3 > ok so the table has duplicated records that really represent older versions of the same file. and what you need is the newest of each file for all files, right?
> I found this sql, but it is untested ( un-understood too ): > > I guess I should add the "order by date desc" as well... > > """SELECT files.* FROM files INNER JOIN (SELECT MAX(id) AS id FROM > files GROUP BY filename) ids ON files.id = ids.id""" > > Last time I tried TurboGears 3 it was not ready for me... Plus, I'll > need to dedicate some time to learn how SQAlchemy works, and I don't > really have the time to dedicate at the moment. > wow really my time machine should be broken. Because I haven't get to that point in time yet. I'm still stuck as far away as TG2 release party. You don't need tg2, SA is default since TG1.1, where the api change is minimal (SO->SA, kid->genshi) > I found documentation for SQLobject on left join, max and group, but > nothing for inner join. And since I can't even decipher it, I can't > try to change the sql to work without having an sql inner join. > how about this http://www.sqlobject.org/SQLObject.html#left-join-and-other-joins it explain how to use left join but it's analogous to the other joins which are listed at the bottom of the section INNERJOIN is the name of your function. > ~Sean > > > On Dec 19, 4:53 am, "Jorge Vargas" <[email protected]> wrote: >> On Fri, Dec 19, 2008 at 12:12 AM, Sean DiZazzo <[email protected]> >> wrote: >> >> > Hi group, >> >> > I'm a MySQL and Turbogears novice. Looking for some help in setting >> > up a database query. I'm trying to query a table by a specific field >> > (not unique), but only have the database return the most recent record >> > of any duplicate record. >> >> > For example: >> >> > Files >> > -------------------------------------- >> > id | filename | date | >> > -------------------------------------- >> > 1 foo.txt 12_07_2008 >> > 2 poo.txt 12_01_2008 >> > 3 foo.text 12_06_2008 >> >> > I'd like to query the db so the resulting SearchResults object only >> > contains records 1 & 2. >> >> sorry but your text doesn't match the example how are records 1 and 2 >> duplicated? Do you have the raw SQL of what you want to accomplish? >> >> On a related note, unless you have a bug code base already build on >> SO, I strongly suggest you start your migration towards SQLAlchemy, >> for many reasons you can search the group for including that overthere >> this is death simple. >> >> > I found some complex sql queries using inner joins, but couldn't >> > figure out how to use them with sqlobject. I also tried to filter the >> > results of a full query, but came to dead ends. >> >> > files = model.Files.select(???) >> >> > Any help would be appreciated! >> >> > ~Sean > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "TurboGears" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/turbogears?hl=en -~----------~----~----~----~------~----~------~--~---
