SQL query to fetch duplicates

Question

I have the following table

people
person_id
first_name
last_name

I want to fetch all the people whose last names occur more than once. Can someone help me with this.

Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Jacob H, Dec 14 '17 at 16:01
You have too much reputation to be asking such poor questions. Where is your research? what have you tried? Why didn't you even use the tag for your RDBMS? — Zohar Peled, Dec 14 '17 at 16:01

score 0 · Answer 1 · answered Dec 14 '17 at 18:36

0

SELECT

Person_id,
Last_Name,

First_Name,

COUNT(*)

FROM

People

GROUP BY

Person_id,Last_Name,First_Name

HAVING

COUNT(*) > 1

answered Dec 14 '17 at 18:36

Chris

61
2

score -1 · Answer 2 · answered Dec 14 '17 at 16:03

-1

Select *
from persons t1
Join (select t2t1. lastName, count(t2t1. *) 'counted' 
          from persons t2t1
          Group by t2t1. lastName
          Having count(t2t1. *) >1
     ) t2 on t1.LastName=t2. Lastname

answered Dec 14 '17 at 16:03

user3532232

257
8
19

SQL query to fetch duplicates

2 Answers2