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.

  • What if it shows up two times in the same set? Jan 26, 2018 at 20:28

4 Answers 4

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


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;
  • Why a primary key is required?
    – McNets
    Jan 26, 2018 at 19:35
  • 1
    @McNets because otherwise there might be two identical rows, say with ('b', 4). On one hand I wouldn't know what the expected output would be in this case and on the other hand a table without a primary or an unique key is not really a table (at least in relational theory ;) Jan 26, 2018 at 22:18

Try this:

FROM table1 a
          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.


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

FROM   test t
   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.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.