Remove 1 instance of duplicate values T-SQL [duplicate]

Question

I currently have a table called People. Within this table there are thousands of rows of data which follow the below layout:

gkey |    Name |  Date       | Person_Id
1    |    Fred |  12/05/2012 | ABC123456
2    |    John |  12/05/2012 | DEF123456
3    |    Dave |  12/05/2012 | GHI123456
4    |    Fred |  12/05/2012 | JKL123456
5    |    Leno |  12/05/2012 | ABC123456

If I execute the following:

SELECT [PERSON_ID], COUNT(*) TotalCount
FROM [Database].[dbo].[People]
GROUP BY [PERSON_ID]
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

I get a return of:

Person_Id     | TotalCount
ABC123456     | 2

Now I would like to remove just one row of the duplicate values so when I execute the above query I return no results. Is this possible?

I can't see duplicate value, Would you like to remove Fred or Leno (who have the same Person_ID) ? Is there a rule for taking such decision ? — Imane Fateh
– Imane Fateh, Commented Jul 4, 2013 at 11:12

t-clausen.dk · Accepted Answer · 2013-07-04 11:17:19Z

7

WITH a as
(
SELECT row_number() over (partition by [PERSON_ID] order by name) rn
FROM [Database].[dbo].[People]
)
DELETE FROM a
WHERE rn = 2

answered Jul 4, 2013 at 11:17

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bvr · Accepted Answer · 2013-07-04 11:11:39Z

0

Try this

DELETE FROM [People]
WHERE gkey IN
(
   SELECT MIN(gkey)
   FROM [People]
    GROUP BY [PERSON_ID]
   HAVING COUNT(*) > 1
)

You can use either MIN or Max

answered Jul 4, 2013 at 11:11

bvr

4,8361 gold badge23 silver badges24 bronze badges

Comments

Mariappan Subramanian · Accepted Answer · 2013-07-04 11:20:31Z

0

DELETE FROM PEOPLE WHERE gkey=
(SELECT MAX(TABLE1.gkey) FROM (SELECT P.gkey, A.PERSON_ID,A.TotalCount FROM People P,(SELECT [PERSON_ID], COUNT(*) TotalCount
FROM [Database].[dbo].[People]
GROUP BY [PERSON_ID]
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC) A WHERE P.gkey=A.gkey) TABLE1 ) TABLE2

answered Jul 4, 2013 at 11:20

Mariappan Subramanian

10.2k9 gold badges34 silver badges34 bronze badges

Comments

Yudhbir Sajwan · Accepted Answer · 2013-07-04 11:22:43Z

0

Use Top keyword with delete;

DELETE TOP(1) FROM [People]
WHERE Person_Id IN
(
   SELECT MIN([PERSON_ID])
   FROM [People]
   GROUP BY [PERSON_ID]
   HAVING COUNT(*) > 1

)

The query is same as posted by Vassy with just Top(1) addded...

answered Jul 4, 2013 at 11:22

Yudhbir Sajwan

311 bronze badge

Collectives™ on Stack Overflow

Remove 1 instance of duplicate values T-SQL [duplicate]

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related