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
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. 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. ~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 -~----------~----~----~----~------~----~------~--~---
