geisterfurz007 geisterfurz007 - 22 days ago 5
SQL Question

SQLite Min function returns NULL in row with String data

Eventhough in every place I found it reads

MIN()
would only return
NULL
if a row is empty, for me it is different.

I have the following table:

+---+------------+------------+------------+
| | column1 | column2 | column3 |
+---+------------+------------+------------+
| 3 | 2016-11-01 | 2016-11-02 | 2016-10-27 |
| 4 | NULL | NULL | 2016-10-28 |
| 5 | 2013-12-12 | 2016-11-02 | 2016-10-30 |
+---+------------+------------+------------+


And try to get the smallest value of each row using

SELECT MIN(column1, column2, column3) FROM myTable


to compare it using

WHERE STRFTIME('%Y-%m-%d', MIN(column1, column2, column3)) BETWEEN DATE('2000-01-01') and DATE('2016-11-10');


However the first line returns the following output:

+-------------------------------+
| MIN(column1, column2, column3 |
+-------------------------------+
| 2016-10-27 |
| NULL |
| 2013-12-12 |
+-------------------------------+


And resulting the
WHERE
statement will not include the row.

What I was hoping for:

+-------------------------------+
| MIN(column1, column2, column3 |
+-------------------------------+
| 2016-10-27 |
| 2016-10-28 |
| 2013-12-12 |
+-------------------------------+


How could I change the statement to not include
NULL
cells or change the table to work properly?

EDIT: There will NEVER be a totally empty row! This table is connected to a Java application that will take care of that.

Answer

This answer isn't nice, and I suspect there might be a better way. But one option would be to use IFNULL on each date column and replace with a really high date value in the case that the value be NULL:

SELECT MIN(IFNULL(column1, '9999-01-01'),
           IFNULL(column2, '9999-01-01'),
           IFNULL(column3, '9999-01-01'))
FROM myTable
WHERE STRFTIME('%Y-%m-%d', MIN(IFNULL(column1, '9999-01-01'),
                               IFNULL(column2, '9999-01-01'),
                               IFNULL(column3, '9999-01-01')))
      BETWEEN DATE('2000-01-01') AND DATE('2016-11-10');
Comments