Marco Marco - 1 month ago 5
MySQL Question

Return NULL values when using min()

I have a query that returns images by a specific month (the min). The query works fine, except that the min never return NULL values, and i need the MIN to include NULL values. I understand that MIN does not return null values, that's why i've tried COALESCE, IFNULL, ISNULL and even LEAST, with no success..

Here's the query

SELECT i.published_date_year, i.published_date_month
FROM image i
JOIN (
SELECT i.published_date_year year, MIN(i.published_date_month) month
FROM image i
WHERE i.is_listed = 1
GROUP BY i.published_date_year
) t1 ON t1.year = i.published_date_year AND t1.month = i.published_date_month
WHERE i.published_date_year = 1967 AND i.is_listed = 1
ORDER BY i.published_date_year, i.published_date_month, i.published_date_day, i.image_id DESC


Notice

MIN(i.published_date_month)


I want to be able to return null values. Looks like MIN is not the correct function, so what is the correct function or formula?

Answer

I'm sure that COALESCE, IFNULL, ISNULL and maybe LEAST worked just fine. The problem is that you use the result afterwards to join with i.published_date_month and obviously nothing equals NULL:

... ON t1.year = i.published_date_year AND t1.month = i.published_date_month

You can add OR t1.month IS NULL to your expression or simply pick an invalid month as dummy value:

... ON t1.year = i.published_date_year
    AND COALESCE(t1.month, 0) = COALESCE(i.published_date_month, 0)

Still, I hardly see how this can make sense. Does image store data without months?

Comments