1

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? – Evan Carroll Jan 26 '18 at 20:28
2
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

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;
  • Why a primary key is required? – McNets Jan 26 '18 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 ;) – ypercubeᵀᴹ Jan 26 '18 at 22:18
1

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.

1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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