As you know Oracle treats NULLs as one "value" in GROUP BY queries. Is there a workaround solution to group data treating NULLs as different values each. For example:
table t:
colA colB
A 1
A 5
<null> 3
<null> 2
select colA, min(colB) from t group by colA
returns:
colA colB
A 1
<null> 2
but i want a query to return:
colA colB
A 1
<null> 3
<null> 2
Thanks in advance
SELECT colA, MIN(colB)
FROM t
WHERE colA IS NOT NULL
GROUP BY colA
UNION ALL
SELECT colA, colB
FROM t
WHERE colA IS NULL
@lc's answer is a great one, but just for the sport here is another solution:
SELECT colA, min(colB)
FROM t
GROUP BY nvl(colA, rownum),colA
And here is a sqlfiddle demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With