MySQL return max value or null if one column has no value

Question

I try to get the max value of a mysql select, but want to have it null/empty/0 if there is one row containing no timestamp.

Table stats (simplyfied):

   ID   CLIENT   ORDER_DATE      CANCEL_DATE

    1     5      1213567200
    2     5      1213567200
    3     6      1210629600      1281736799
    4     6      1210629600      1281736799
    5     7      1201042800      1248386399
    6     7      1201042800      
    7     8      1205449200      1271282399

I'm now looking to get the lowest order date (no problem, as it is never empty), and the maximum cancel date. If the client has already cancelled his subscription, the cancel date is filled, but if he is still active, there is no cancel date at all.

Query:

SELECT ID, min(ORDER_DATE) AS OD, max(CANCEL_DATE) AS CD FROM stats GROUP BY CLIENT

Returns:

ID    OD           CD
 5    1213567200                  // fine
 6    1210629600   1281736799     // fine
 7    1201042800   1248386399     // Should be empty
 8    1205449200   1271282399     // fine

I can't figure it out how to return empty/0/NULL if there is one (or more) empty colums for a client. Also tried with NULL fields.

Thanks for any hint.

score 7 · Accepted Answer · edited Aug 02 '13 at 10:20

7

I don't know how fast it will be but I guess it can be solved like this:

SELECT ID, min(ORDER_DATE) AS OD,
IF(COUNT(*)=COUNT(CANCEL_DATE),max(CANCEL_DATE),NULL) AS CD 
FROM stats GROUP BY CLIENT

I couldn't test it but the idea behind this solution is that count(cancel_date) should count all not null value entries and if it's equal to count(*) that means that there are no null values and it will return max(cancel_date), otherwise null.

edited Aug 02 '13 at 10:20

toro2k

19,020
7
64
71

answered Aug 02 '13 at 10:02

Petr Novicky

86
2

i don't know if it will be faster than mine, i think they might be equivalent, but it's a nice idea :) +1 – fthiella Aug 02 '13 at 10:12
Working, too. Thanks a lot. Was fiddeling around for hours, but didn't got that simple solution. Speed is: 0.06sec for ~6k rows. So a bit faster than fthiella's solution – Yenky Aug 02 '13 at 10:24

score 4 · Answer 2 · edited Jan 11 '18 at 09:35

You could use a query like this:

SELECT
  client,
  min(ORDER_DATE) AS OD,
  case when MAX(CANCEL_DATE IS NULL)=0 THEN max(CANCEL_DATE) END AS CD
FROM
  stats
GROUP BY
  CLIENT

Please see fiddle here.

CANCEL_DATE IS NULL will be evaluated either to 0, when CANCEL_DATE is not null, or to 1 when it is null
MAX(CANCEL_DATE IS NULL) will be evaluated to 0 if there are no cancel_date with null values, otherwise its value will be 1.
when MAX(CANCEL_DATE IS NULL)=0 it means that there are no rows where CANCEL_DATE is null, and we need to return MAX(cancel_date) in that case, otherwise we need to return NULL.

For speed: needs 0,07sec on 6k rows – Yenky Aug 02 '13 at 10:23 — Yenky, Aug 02 '13 at 10:23

MySQL return max value or null if one column has no value

2 Answers2

Linked