CODEWITHSUNDEEP
sqlpostgresqlduplicates
Sawan Doijode
Sawan Doijode

Reputation: 43

postgresql find duplicates in column with ID

For instance, I have a table say "Name" with duplicate records in it:

Id    |  Firstname
--------------------
1     |  John
2     |  John
3     |  Marc
4     |  Jammie
5     |  John
6     |  Marc

How can I fetch duplicate records and display them with their receptive primary key ID?

Upvotes: 4

Views: 14555

Answers (2)

kjmerf
kjmerf

Reputation: 4345

SELECT t.*
FROM t
INNER JOIN
(SELECT firstname
 FROM t
 GROUP BY firstname
 HAVING COUNT(*) > 1) sub
ON t.firstname = sub.firstname

A sub-query would do the trick. Select the first names that are found more than once your table, t. Then join these names back to the main table to pull in the primary key.

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93704

Use Count()Over() window aggregate function

Select * from
(
select Id, Firstname, count(1)over(partition by Firstname) as Cnt
from yourtable
)a
Where Cnt > 1

Upvotes: 10

Related Questions