CODEWITHSUNDEEP
sql-servert-sql
Sahar
Sahar

Reputation: 3

How can I find duplicate on one column

I have a SQL server database,and there are many duplicate in one(RanjePhoneNumber) column. I am trying to select rows from a table that have duplicates in RanjePhoneNumber column and they have a same CityId.

My Table:

RanjePhoneNumber    ContactId   CityId
776323          280739      7
342261          186372      80
468284          75980       7
776323          101969      9
362875          170242      13
224519          164914      7
342261          203606      55
776323          280733      7
342261          203602      80

My expected results:

RanjePhoneNumber    ContactId   CityId
776323          280739      7
342261          186372      80
776323          280733      7
342261          203602      80

Upvotes: 0

Views: 28

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460158

Group by those two columns:

SELECT RanjePhoneNumber, CityID
FROM dbo.TableName
GROUP BY RanjePhoneNumber, CityID
HAVING COUNT(*) > 1

If you want to select all columns you could use a ranking function:

WITH CTE AS
(
    SELECT t.*, Cnt = COUNT(*) OVER (PARTITION BY RanjePhoneNumber, CityID)
    FROM dbo.TableName
)
SELECT RanjePhoneNumber, ContactId, CityId 
FROM CTE
WHERE Cnt > 1

If you don't want to find all rows which belong to this "duplicate-group" but only all but the first, use the ROW_NUMBER approach the other answer has shown.

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28900

;with cte
as
(select 
Ranjephonenumber,
contactid,
cityid,
row_number() over (partition by Ranjephonenumber,cityid order by cityid) as rn
from table
)
select 
Ranjephonenumber,contactid,city from cte where rn>1

Upvotes: 1

Related Questions