Select unique subsets

Question

I have a table like in example below.

SQL> select * from test;

    ID  PARENT_ID NAME
     1          1 A
     2          1 B
     3          2 A
     4          2 B
     5          3 A
     6          3 B
     7          3 C
     8          4 A

What I need is to get all unique subsets of names ((A,B), (A,B,C), (A)) or exclude duplicate subsets. You can see that (A,B) is twice there, one for PARENT_ID=1 and one for 2. I want to exclude such duplicates:

ID  PARENT_ID NAME
1          1 A
2          1 B  
5          3 A
6          3 B
7          3 C
8          4 A

I removed the incompatible database tags. Tag only with the database you are using. — Gordon Linoff, May 15 '17 at 17:55
The one you posted below, is that the desired result? (Just clarifying) — Robin Carlo Catacutan, May 15 '17 at 17:59
Possible duplicate of [Finding duplicate values in a SQL table](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — polka, May 15 '17 at 18:00
In the end I don't need ID. I need to have PARENT_IDs and NAMes because I need them for another SQL statements. — mumbojambo, May 15 '17 at 18:37

India.Rocket · Answer 1 · 2017-05-15T18:26:59.797

0

I have used 'group_concat' assuming you are using 'Mysql'. The equivalent function in Oracle is 'listagg()'. you can see it in action here in SQL fiddle

Here is the solution:-

Select a.* from
test a
inner join
(
Select nm, min(parent_id) as p_id
from
    (
    Select Parent_id, group_concat(NAME) as nm
    from test
    group by  Parent_ID
    ) a
group by nm
)b
on a.Parent_id=b.p_id 
order by parent_id, name

edited May 15 '17 at 18:26

answered May 15 '17 at 18:10

India.Rocket

1,225
1
8
11

I also found that 'group_concat' equivalent on Oracle will help but I thought it's a bad idea (performance reasons; haven't thested it though). – mumbojambo May 15 '17 at 18:28

Robin Carlo Catacutan · Answer 2 · 2017-05-15T18:18:34.563

0

You can use DISTINCT to only return different values.

e.g.

SELECT DISTINCT GROUP_CONCAT(NAME SEPARATOR ',') as subsets 
FROM TABLE_1
GROUP BY PARENT_ID;

SQL Fiddle

edited May 15 '17 at 18:18

answered May 15 '17 at 18:11

Robin Carlo Catacutan

13,249
11
52
85

Except based on the output, the question asks for the Parent_ID and names to be kept separate. and based on output keep the minimum parent_ID only if duplicates exist. – xQbert May 15 '17 at 18:22
1

@xQbert yeah that's what I'm really wondering if how he wanted to see the results. If `((A,B), (A,B,C), (A))` or the one he posted at the bottom. But still I haven't got his confirmation yet. So I decide to give an answer as he might want this format. – Robin Carlo Catacutan May 15 '17 at 18:27

Select unique subsets

2 Answers2