# 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`.

• What if it shows up two times in the same set? – Evan Carroll Jan 26 '18 at 20:28

``````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 '18 at 19:35
• @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

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.

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: