CODEWITHSUNDEEP
sqlpostgresql
nit710
nit710

Reputation: 164

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:

1,NULL,3

as the result.

Upvotes: 0

Views: 80

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

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.

Upvotes: 2

sstan
sstan

Reputation: 36543

You can do this by doing a count distinct:

select 
  case when count(distinct col1) = 1 then min(col1) end as col1,
  case when count(distinct col2) = 1 then min(col2) end as col2,
  case when count(distinct col3) = 1 then min(col3) end as col3
from tbl

Upvotes: 2

Kacper
Kacper

Reputation: 4818

select case when count(distinct col1) = 1 then max(col1) else null end,
       case when count(distinct col2) = 1 then max(col2) else null end,
       case when count(distinct col3) = 1 then max(col3) else null end
from table;

Upvotes: 2

Related Questions