Return NULL values when using min()

Question

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?

tried MIN(IFNULL(i.published_date_month, 0)) month but then no images are returned because there is no such month that equals 0 — Marco
– Marco, Commented Oct 4, 2016 at 19:06

trincot · Accepted Answer · 2016-10-04 19:10:48Z

2

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.

answered Oct 4, 2016 at 19:10

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Álvaro González · Accepted Answer · 2016-10-05 07:18:59Z

1

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?

edited Oct 5, 2016 at 7:18

answered Oct 4, 2016 at 19:13

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

4 Comments

Marco Over a year ago

the OR t1.month IS NULL did not work, i got no results. And yes, most of the time, i have no clue what the day and month and even the year of the image is.

Marco Over a year ago

that is exactly my problem. i've added MIN(IFNULL(i.published_date_month, 0)) month and changed the afterwards formula to AND COALESCE(t1.month, 0) = COALESCE(i.published_date_month, 0) and all works fine!! Now, i just need to study the formula, make sure i understand all those COALESCE 's.. thanks!!

Álvaro González Over a year ago

Please note the OR t1.month IS NULL part is not full code you can just append—I was just calling your attention towards IS NULL (vs =). You'd need to ensure that both involved columns are null at the same time.

Álvaro González Over a year ago

As about COALESCE(), it's rather similar to other functions you mention. I tend to use it because it's a SQL standard function that works in all DBMS I work with.

ScaisEdge · Accepted Answer · 2016-10-04 19:03:48Z

1

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

answered Oct 4, 2016 at 19:03

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Return NULL values when using min()

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related