CODEWITHSUNDEEP
mysql
Marco
Marco

Reputation: 2737

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?

Upvotes: 0

Views: 3507

Answers (3)

Álvaro González
Álvaro González

Reputation: 146410

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 an expression built around 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?

Upvotes: 1

trincot
trincot

Reputation: 350147

You could check for NULL separately with a CASE WHEN:

SELECT CASE WHEN MAX(i.published_date_month IS NULL) = 0 
          THEN MIN(i.published_date_month)
       END

This returns NULL when there is at least one NULL found in i.published_date_month.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You can use an ifnull and a case 

   SELECT 
      i.published_date_year year
   , case MIN(ifnull(i.published_date_month, 0))
     when 0 then null
     else   MIN(ifnull(i.published_date_month, 0)) 
     end month

Upvotes: 1

Related Questions