SQLite: query to return either distinct value or NULL

Question

In an sqlite3 database, I have a column called date. I need a query that, if all date values are identical, returns the identical value. If they are not, it should return NULL.

How can I best express this in SQL?

ypercubeᵀᴹ · Accepted Answer · 2013-10-24 14:26:35Z

SELECT CASE WHEN MIN(DateColumn) = MAX(DateColumn) THEN MIN(DateColumn) ELSE NULL -- not needed, added for clarity END AS result FROM TableX ;

Not sure about SQLite but in most DBMS, if there is an index on (DateColumn) the MIN/MAX condition will be much more efficient than any query with COUNT().

thanks for the quick answer. Is it better to use min and max or to write WHEN COUNT(DISTINCT DateColumn) = 1?

Teja · Accepted Answer · 2013-10-24 14:35:53Z

SELECT CASE WHEN COUNT(DISTINCT DateColumn) = 1 THEN DateColumn ELSE NULL END AS result FROM YourTable;

Collectives™ on Stack Overflow

SQLite: query to return either distinct value or NULL

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related