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.
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.
I guess another alternative would be to use a View for the UNIONized
query, but doesn't MySQL 'fake' views in 5.0 somehow?
Rob Wultsch wrote:
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?
--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
