Hi Derek --
Yes, I need more columns. I need the name of the meet where the best time was achieved. And the club the athlete was a member of, and the date of the fastest time, etc. I can't put all of those columns in the group and ask for "min" of each because the min of each column will be from different rows of the table. I want the minimum time, AND the name of that exact meet -- not the minimum time and then the "min" across all meet names. That's why I need to find the min time in one query and then use that to look up the row where it occurs, so I can get the rest of the data. See my finished page in my response to Richard and maybe it will make more sense. It's hard to put database stuff into words! https://groups.google.com/d/msg/web2py/iQQ3lm7E9wg/aqUnMhR60s8J Warm regards, Joe B. On Wednesday, February 19, 2014 9:53:28 AM UTC-8, Derek wrote: > > I don't get what you are talking about. you are grouping by the event_code > and the course, then the min should give you the minimum for that > particular event code and course. You want more columns then group by them > as well. > > On Wednesday, February 19, 2014 4:29:55 AM UTC-7, Joe Barnhart wrote: >> >> 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.
