nit710 nit710 - 1 year ago 64
SQL Question

get columns which have same value in all rows

suppose following is my table:-

COL1 | COl2 | COL3
-----|----- |-----
1 | 2 | 3
1 | 2 | 3
1 | 4 | 3

I want record from each column which has a single value in all row.

For the above sample data, I want:


as the result.

Answer Source

You can use aggregation and case. For your data:

select (case when min(col1) = max(col1) then min(col1) end) as col1,
       (case when min(col2) = max(col2) then min(col2) end) as col2,
       (case when min(col3) = max(col3) then min(col3) end) as col3
from t;

I strongly recommend using min() and max() instead of count(distinct). The latter tends to have poor performance.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download