emkey08 emkey08 - 6 months ago 9
SQL Question

SQL - select identical value not listed in GROUP BY

In SQL, is it somehow possible to select an identical value (if existent) of a column which is not listed within the

GROUP BY
clause?

In other words, I'd like to be able to do something like this:

SELECT a, IDENTICAL_VALUE(b)
FROM x
GROUP BY a;


Whereby
IDENTICAL_VALUE
should return the value of
x.b
if it's identical (i.e. unique), and
NULL
otherwise.

Answer

You can count the distinct values and just return the MAX if there's only one.

SELECT a, 
       CASE WHEN COUNT(DISTINCT b) =1 THEN MAX(b) END
FROM x 
GROUP BY a;

Or, alternatively (may be better performing as no need to actually identify the distinct values)

SELECT a, 
       CASE WHEN MAX(b) = MIN(b) THEN MAX(b) END
FROM x 
GROUP BY a;