select distinct without including duplicates

Question

I have a table looking like the following and I need a table with the unique values of Id and another one with the Id values repeated.

My problem is that I can't use a "select distinct" statement because it will include the repeated Id values once.

Id letter

80004 A

80008 B

80019 C

80086 A

80086 C

80086 B

80066 A

80099 C

80100 A

80087 C

80087 A

What I need is to divide the previous table into two tables looking like:

Id letter

80004 A

80008 B

80019 C

80066 A

80099 C

80100 A

and

Id letter

80086 A

80086 C

80086 B

80087 C

80087 A

I just want a table with those Id that only have one group (like 80004, 80008...) and their group. I also need another table with those Id that can have different groups (80086 and 80087) and their groups (A,C and B for 80086, and C and A for 80087). — Judit
– Judit, Commented Jun 21, 2016 at 11:19
But why is 80004,80008 is a group and not 80004,80086 ?? What's the logic behind this — sagi
– sagi, Commented Jun 21, 2016 at 11:20
instead of saying that 80004 and 80008 I'd say that is a letter (I edit the question) — Judit
– Judit, Commented Jun 21, 2016 at 11:24

Houari · Accepted Answer · 2016-06-21 11:27:40Z

By using a sub query:

select A.id , id_value from ( select id from mytable group by id having count(id_value) >1 ) A inner join mytable using(id) select A.id , id_value from ( select id from mytable group by id having count(id_value) <=1 ) A inner join mytable using(id)

Here is the SQLFIDDLE

Collectives™ on Stack Overflow

select distinct without including duplicates

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related