This my original table:
Intended table:
I can't add a new column to table.
3
-
4How do you decide which ones are the "good ones" and which ones are the "bad ones"?The Impaler– The Impaler2019-05-20 20:43:05 +00:00Commented May 20, 2019 at 20:43
-
5I think the main issue here originates in the absence of a primary key.The Impaler– The Impaler2019-05-20 20:47:01 +00:00Commented May 20, 2019 at 20:47
-
1SQL Server questions need to show an attemptDale K– Dale K2019-05-20 20:48:22 +00:00Commented May 20, 2019 at 20:48
Add a comment
|
3 Answers
You can delete duplicates using i.e. ROW_NUMBER():
with duplicates as
(
select
*
,ROW_NUMBER() OVER (PARTITION BY FirstName, LastName, age ORDER BY FirstName) AS number
from yourTable
)
delete
from duplicates
where number > 1
Each row where number is bigger than 1 is a duplicate.
1 Comment
S3S
careful, you didn't partition by
Age as well which is needed to remove true duplicatesYou can use a common table expression to delete all but the first record in a set of duplicates:
with cte as (
select *, row_number()
over (partition by FirstName, LastName, Age order by FirstName, LastName, Age) as row_number
from tbl
)
delete from cte where row_number <> 1
Comments
You need some way of identifying the rows in the absence of a PK.
The only way I can think of is to:
- Retrieve the "Row IDs" of the rows,
- Then pick one as the "good one" for every non-unique row,
- And finally delete all the other ones.
I'm not positive about this solution, though. I think this answer can help to produce distinctive row ids. I hope it helps.
