mysql min ignores null - need workaround/tweak

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 i开发者_如何学God.

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 ?

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.

select case when count(date)=sum(1) then min(date) else null end

or, when all rows are not null, return min date, else return null.

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

If the question is about finding the minimum date from multiple columns, then the following could help;

SELECT 
      LEAST
            (IFNULL(date_1, date(CURRENT_DATE()+1)),
            IFNULL(date_2, date(CURRENT_DATE()+1)),
            IFNULL(date_3, date(CURRENT_DATE()+1))) as MinimumDate
FROM YourTable;