MySQL Max(isnull(date_field)) or max(coalesce(date_field))
I have a query reading a date from a parent/child relationship and i need to group the records by the max of the date, from whichever tables this comes. My query:
SELECT child.id as id,
MAX(pm.start),
MAX(membership.start),
MAX(IFNULL(pm.start,membership.start)) AS start
FROM organisation child
LEFT JOIN organisation pmorg ON child.parent_organisation_id = pmorg.id
LEFT JOIN membership ON child.id = membership.organisation_id
LEFT JOIN membership pm ON pmorg.id = pm.organisation开发者_C百科_id
WHERE child.id IN (1,3) AND /*just look at example records*/
coalesce(pm.start,membership.start) IS NOT NULL AND
((membership.active = 1 AND membership.amount_paid > 0.00 ) OR
(pm.active = 1 AND pm.amount_paid > 0.00))
GROUP BY child.id
My Output is strangely:
1 NULL 2011-07-01 00:00:00 323031312d30372d30312030303a30303a3030
3 2011-07-01 00:00:00 NULL 323031312d30372d30312030303a30303a3030
I need that last column to be 2011-07-01 and I need both rows. Any idea why max(coalesce()) doesn't work? I'm running MySQL 5.1
Stupidly this query only breaks in PHPMyAdmin and works completely fine in mysql command line. So no issue here at all!
精彩评论