Select unique values for one column There is a table with different sets a(1,2), b(2,3,4) and etc.

-- table1 --
set_name | value
a        | 1
a        | 2
b        | 2
b        | 3
b        | 4
c        | 3
c        | 5
...      | ...


How to get a table containing name of set with values which is unique for all table? Result should be like this:

-- table2 --
set_name | value
a        | 1
b        | 4
c        | 5
...      | ...


Since value 2 is in set a and set b, it's excluded; since value 3 is in sets b and c, it's excluded....

In other words, I want to see the rows where the value is listed for one and only one set_name.
create table test(set_name text, value int);
insert into test values
('a', 1),('a', 2),('b', 2),('b', 3),
('b', 4),('c', 3),('c', 5);



You can use a Window function and a CTE (you can move the CTE part to a subquery), to get the desired result.




;with x as
(
    select set_name, value, 
           count(value) over (partition by value) cnt
    from   test
)
select set_name, value
from   x
where  cnt = 1;



set_name | value
:------- | ----:
a        |     1
b        |     4
c        |     5



dbfiddle here