CODEWITHSUNDEEP
sqlduplicatessql-deletemysql4
simpatico
simpatico

Reputation: 11087

SQL to delete duplicate records with some common field values?

Consider a table with the following schema:

id, location, starred

There are many records with the same location:

id | location | starred
-----------------------
1     rome      yes
2     rome      no
3     rome      no
4     milan     yes
5     milan     no
6     bozen     no

I want to have at most one record per location. And given the choice between a starred record and a not starred record I want the starred. So what sql will produce this table:

id | location | starred
-----------------------
1     rome      yes
4     milan     yes
6     bozen     no

I suspect this could be done with some virtual tables or ªviews'. 

DELETE FROM table
GROUP BY location,

Upvotes: 1

Views: 6533

Answers (4)

Void Ray
Void Ray

Reputation: 10199

If [started] can only be yes, or no, then this should work:

create table data
(
id int identity(1,1),
location varchar(50),
[started] varchar(3)
)

insert into data select 'Rome', 'Yes'
insert into data select 'Rome', 'No'
insert into data select 'Rome', 'No'
insert into data select 'Milan', 'Yes'
insert into data select 'Milan', 'No'
insert into data select 'Bozen', 'No'

WITH locationsRanked (id, location, [started], rank)
AS
(
    select min(Id), location, [started],
    RANK() OVER (PARTITION BY location  ORDER BY location, [started] DESC) AS Rank
    from data
    group by location, [started]
)
select * from locationsRanked where Rank = 1
order by id

Upvotes: 1

Ramesh Manni
Ramesh Manni

Reputation: 106

Use Analytical functions to delete duplicates. Following code generates row_number based location and sorted by starred desc ( so yes comes first)

 delete from mytable2 where id in ( 
select id from
( select  id, location,starred,row_number() over ( partition by location order by location, starred desc) row_num 
  from mytable2
) where row_num >1
)

Upvotes: 3

Dave
Dave

Reputation: 3621

If you're just wanting to extract the data something like this should work:

select
    [table].*
from
    [table]
    inner join (select 
                    MIN(id) as id, 
                    location 
                from 
                    [table] 
                group by location) as data
        on [table].id = data.id

Obviously you could also use the results of this (or a similar query) to determine a list of ids to delete.

Upvotes: 0

Robuust
Robuust

Reputation: 1

I think this can help you:

http://www.w3schools.com/sql/sql_distinct.asp

Upvotes: -1

Related Questions