mysql min ignores null - need workaround/tweak

Question

For this query

SELECT min(date) min_date FROM  order_products group by order_id

min_date ignores the NULL values in a group of order id.

In my case I want min_date = NULL if the group has a order_products tuple with null value in date.

Any idea how can I achieve this ?

Answer 1

One of the ways of doing that, if your problem domain allows defining a "absolute minimum date", is to use ISNULL()/COALESCE() expression to turn a nullable field into non-null.

ISNULL(dateField, '1000-01-01')

Another (f no meaningful "smallest" default can be defined) is to do it via 2 queries - first select stuff WITH nulls, then if the result set is empty, add in stuff where date is not null

Answer 2

A simple workAround could be:

SELECT min(IFNULL(date, '1970-01-01')) AS min_date 
FROM order_products 
GROUP BY order_id

So, if 1970/01/01 turns up, you know that there's a NULL value.