Delete duplicate records and keep one [duplicate]

Question

Using T-SQL.

My problem is I have a list of usersid and locationid. A user can only have one location.

With the following query I get a list with all users with multiple location id's:

SELECT * 
FROM Userslocation
WHERE [USER] IN (
    SELECT [USER]  
    FROM Userslocation
    GROUP BY [User] 
    HAVING COUNT(*) > 1
    )

as result I get

location User
80653    83269
63150    83269
80653    84035
63150    84035
80653    85097
63150    85097

I want to get preserve one result and delete the rest

location User
80653    83269
80653    84035
80653    85097

How do you decide which records to keep...and which to delete? — Mr Moose
– Mr Moose, Commented Jul 9, 2015 at 6:32

Shiva · Accepted Answer · 2015-07-09 06:38:16Z

1

You'll need something like a CTE

WITH UserslocationCTE AS (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY [User] 
    ORDER BY location)'RowRank'
    FROM Userslocation)
DELETE FROM UserslocationCTE 
WHERE RowRank > 1

answered Jul 9, 2015 at 6:38

Shiva

21.1k15 gold badges88 silver badges119 bronze badges

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

Comments

jarlh · Accepted Answer · 2015-07-09 06:33:06Z

0

Return each user once, with the user's max location:

select user, max(location)
from tablename
group by user
having count(*) > 1

answered Jul 9, 2015 at 6:33

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

nayef harb · Accepted Answer · 2015-07-09 07:33:21Z

Insert an Incremental Id for the usersLocation. Then Delete the records which are greater than the first ID. for example.

Query
___
SELECT * 
FROM Userslocation
WHERE [USER] IN (
    SELECT [USER]  
    FROM Userslocation
    GROUP BY [User] 
    HAVING COUNT(*) > 1
    )

will show the below result

ID   location User
1    80653    83269
2    63150    83269
3    80653    84035
4    63150    84035
5    80653    85097
6    63150    85097

now you can do

 SELECT *
--or delete 
FROM Userlocation
WHERE id NOT IN (
        SELECT MIN(id)
        FROM Userlocation
        GROUP BY [USER]
        HAVING COUNT(*) > 1

        UNION

        SELECT MIN(id)
        FROM Userlocation
        GROUP BY [USER]
        HAVING COUNT(*) = 1
        )

Collectives™ on Stack Overflow

Delete duplicate records and keep one [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related