Select unique values for one column

Question

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.

Answer 1

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

Answer 2

If the table has only these 2 columns - and no more - then you could use a simpler query (and I assume that the table has a primary key):

select min(set_name) as set_name, value 
from table1
group by value
having min(set_name) = max(set_name) 
order by set_name;

Answer 3

Try this:

SELECT
   a.set_name,
   b.value
FROM table1 a
INNER JOIN (
   SELECT
      value,
          COUNT(1) AS val_count 
       FROM table1
       GROUP BY value
       HAVING COUNT(1) = 1
    ) b ON a.value = b.value
ORDER BY a.set_name

To break down the logic of the script:

1. I get first the list of values in value column where there is only 1 instance of it.
2. Then joined it on the original table with the join clause of the value column.

This will surely work for a simple table like in your example. If you have a very large data, lets try to optimize this query.

Answer 4

A NOT EXISTS semi-anti-join should be intuitive:

SELECT *
FROM   test t
WHERE  NOT EXISTS (
   SELECT 1
   FROM   test
   WHERE  value = t.value
   AND    set_name <> t.set_name
   );

dbfiddle here _{(building on McNets fiddle)}

This returns whole rows (or any arbitrary selection of columns).
Assuming both columns to be defined NOT NULL - or you have to declare how you intend to deal with NULL values (consider equal or not?).

An index on (value, set_name) (columns in this order) helps performance with big tables a lot.

Related:

• How do I (or can I) SELECT DISTINCT on multiple columns?