Re: Group-wise Maximum performance

[Preston McMurry]

It seems to me -- and I may be wrong -- that for each record in the database, you do the subselect to see if the record is equal to whatever the max is. Thus, if you have 20,000 records in the database, you are looking at 20,000 records for each. In other words, 20,000 * 20,000 records. That would certainly slow things down. It would be better to do one query to determine the max (20,000 hits), then take that value and in a second query look for records which have that as the time (20,000 more hits) for a total of 20,000 + 20,000 (or LOTS less)

http://prestonm.com ... http://www.mcmurry.com