Hello, I'm sorry that this is not MaxDB-specific question, but a more general SQL problem. Anyway, maybe someone could help me:
I have the following query (the main purpose of the query is to get latest measurements for a lake.) My problem is that ANY statement does not like NULL's. If for some reason dbc_measurements.date_taken is NULL, then ANY statement does not work (return value undefined, according to MaxDB manual. How can I rewrite the query so that it does not ignore rows containing NULL? SELECT DISTINCT(l.id),l.name FROM dbc_measurements t1 , dbc_lakes l WHERE t1.param_id=46 AND t1.lake_id=l.id AND (t1.date_taken, t1.lake_id) = ANY (SELECT MAX(date_taken), lake_id FROM dbc_measurements WHERE param_id=46 AND measurement>1 GROUP BY lake_id) ORDER BY l.name The query bellow seems to work, but the cost value, according to optimizer, is 100 times higher: SELECT DISTINCT(l.id),l.name FROM (SELECT lake_id FROM (SELECT MAX(date_taken), lake_id FROM dbc_measurements WHERE param_id=46 AND measurement>1 GROUP BY lake_id)) t1, dbc_lakes l WHERE t1.lake_id=l.id ORDER BY l.name Greetings, Andris Spruds -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
