Hi Derek -- Thanks for your response. I had considered your approach, but just declaring min() for every column does not return the values I need. Take for example the min time and the minimum "meet name". Yes, I could get the lexical minimum of a character string that represents the meet at which the best time was recorded -- only it wouldn't be correct. The min(time) and min(meet_name) would have no relationship to one another -- they would be from completely different rows.
That's why I must find the minimum time in one query, then use it to probe the entire table to find the full row it came from and read the other values. I've been pouring over the web looking for an alternative, but I just can't find one for my situation. Fortunately, I've been able to create a string and use "executesql" to get the query I need. It took some doing, but I managed to get the query into a single "inner join" and with proper indexing it is amazingly fast. I clocked it at about 20ms for a typical query on my data set. I was still able to make the result a proper Rows object and use SQLTABLE to style the output. Warm regards, Joe B. On Tuesday, February 18, 2014 11:53:19 AM UTC-8, Derek wrote: > > Joe, it seems to me all you need is this part here: > select course, event_code, min(finals) as "min_time" from times > where id_competitor=XXXX and finals>0 > group by course, event_code > > Or are you interested in returning other columns within the 'times' table? > if so, just use min(other columns) in the query, no need for a derived > table. > > So, in any case, web2py works a little differently. Taking the book > example and modifying it, here's what I get... > > for row in db(db.times.competitor==xxxx).select( > db.times.course, db.times.event_code, min_time, > groupby=(db.times.course,db.times.event_code)): > print row.times.course, row[min_time] > > What you need to do before that can work is to create the min_time > operator... > > min_time = db.times.finals.min() > > oh, but you wanted to only select non negative numbers, and finals has > some negatives in it... (add a where clause to the select) > Good luck! > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
