On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko <[EMAIL PROTECTED]> wrote: > GROUP BY seems like an obvious choice; 'GROUP BY username', to be > exact. However, this seems to produce not the last row's values, but ones > from a random row in the group. Under most databases your query is erroneous. Take a look at http://lists.mysql.com/mysql/212084 .
> I don't think the fact that I am doing this on a subquery is > relevant, but just in case, I am including this info. > > Here is what the query looks like, abridged: > > > SELECT id,username,log_date,event_type > FROM (SELECT * FROM a > UNION > SELECT * from b) as h > GROUP BY username Read http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Your are probably going to end up with a fairly ugly query (mostly because of the union) with what you have a derived table which will join against a and b again. SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ... FROM (SELECT username, MAX(log_date) as mlog_date FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username) AS a1 LEFT JOIN a AS a2 ON a1.mlog_date = a2.log_date AND username... LEFT JOIN b AS b2 ... Any one have a suggestion for how to do with in a way that is not ugly as heck? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
