On Fri, Apr 11, 2008 at 4:01 PM, Victor Danilchenko <[EMAIL PROTECTED]> wrote: > Oooh, this looks evil. It seems like such a simple thing. I guess > creating max(log_date) as a field, and then joining on it, is a solution -- > but my actual query (not the abridged version) is already half a page long.
A derived table or a LEFT JOIN are your best bets, as shown here: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html In most cases, the derived table is best. It creates a temp table automatically, so it's similar to using a view. My experiments with actual views gave dismal performance, and the user variable trick described on Baron's blog is pretty hard to get right. > I think at this point, unless someone else suggests a better > solution, this would be easier to do programatically -- skip the group > altogether, and instead simply order the rows, and grab the last one for > each username in code. If you don't have that much data to worry about then this could be good, but it's often tricky to code correctly because of the state you have to keep track of. Also, use UNION ALL if you don't need MySQL to remove duplicate rows. It makes a big difference in performance. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
