Postgresql – Select unique values for one column

duplicationpostgresql

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.

Best Answer

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

Related Solutions

Sql-server – Select one name per person where spelling variations exist

You can "group by" Emp_ID and use an aggregate function like MIN() or MAX() to get one of the names:

INSERT INTO TargetTable 
  (Emp_ID, Name) 
SELECT Emp_ID, MIN(Name) 
FROM SourceTable 
GROUP BY Emp_ID ;

And note that there is no inherent order in a table (actually you can define a clustered index for a table and this affects how the rows are stored on the disk but that is no guarantee for the order of retrieval).

You can get the data from the target table with a query afterwards and if you want them ordered, you can (and should) define the order you like (and a different one if you like, every time you query it):

SELECT Emp_ID, Name 
FROM TargetTable 
ORDER BY Name ;

Postgresql – Create a new table (multiple columns) by selecting random values from an existing table

I suggest a "data-modifying CTE":

WITH cte AS (
   SELECT *, row_number() OVER () AS rn
   FROM  (
      SELECT id
      FROM   tbl
      ORDER  BY random()
      LIMIT  600  -- 2 x 300
      ) sub
   )
INSERT INTO table2(source, target)
SELECT c1.id, c2.id
FROM   cte c1
JOIN   cte c2 ON c2.rn = c1.rn + 300;

In the CTE:

select 600 random rows (to create 300 new rows)
add a row number in the outer SELECT.

Then couple two values in a self join with 300 offset.

To get random rows from a huge table cheaply, consider:
Best way to select random rows PostgreSQL

Best Answer

Related Solutions

Sql-server – Select one name per person where spelling variations exist

Postgresql – Create a new table (multiple columns) by selecting random values from an existing table

Related Question