CODEWITHSUNDEEP
postgresqlduplicatesdistinct-values
Judit
Judit

Reputation: 3

select distinct without including duplicates

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

Upvotes: 0

Views: 89

Answers (1)

Houari
Houari

Reputation: 5621

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

Upvotes: 1

Related Questions