sql - vertica check if unique elements for each group from two columns are identical -

sql - vertica check if unique elements for each group from two columns are identical -

table in vertica:

gid b 1 2 2 1 3 2 1 1 1 2 2 1 2 1 2 2 1 1 3 1 1 3 2 1

note, values in 2 columns not distinct given gid (see gid=2) each gid, want check if unique elements in col same unique elements in col b, if equal, status = 1 else 0. expected result be:

gid status 1 0 2 1 3 0

how accomplish in vertica or sql?

assuming values in 2 columns distinct given gid, can full outer join , group by:

select coalesce(t.gid, t2.gid) gid, (case when count(t.gid) = count(*) , count(t2.gid) = count(*) 1 else 0 end) invertica t total outer bring together invertica t2 on t.gid = t2.gid , t.a = t2.b grouping coalesce(t.gid, t2.gid);

if values not distinct, need clarify question specify whether counts need same in each column. (if don't care counts, above work.)

edit:

you express using not exists:

select t.gid, max(val) (select t.gid, (case when not exists (select 1 invertica t2 t.gid = t2.gid , t.a = t2.b) 0 when not exists (select 1 invertica t2 t.gid = t2.gid , t.b = t2.a) 0 else 1 end) val invertica t ) t grouping t.gid;

sql comparison unique vertica