I originally posted this on the turbogears list, but they suggested I post it here.

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: 

id   |   filename   |   date   | 
1       foo.txt         12_07_2008 
2       poo.txt        12_01_2008 
3       foo.txt       12_06_2008 

I'd like to query the db so the resulting SearchResults object only contains records 1 & 2. 

I found this query that gives me the correct results:

"""SELECT files.* FROM files INNER JOIN (SELECT MAX(id) AS id FROM files GROUP BY filename) ids ON files.id = ids.id""" 

The problem is that i can only run it with the _connection.queryAll() function which only returns a list of tuples.  I need a SelectResults object to work with my Turbogears datagrid widget.

Is there any way to cast the list of tuples to a sqlobject.sresults.SelectResult object?  Or to run the query so that it directly returns a SelectResults object? 

I started to make progress using sqlbuilder until I realized it just builds the query that I already have...

What's the best way to accomplish this?