SQL to delete duplicate records with some common field values?

Question

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, 

Answer 1

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

Answer 2

I think this can help you:

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

Answer 3

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.

Answer 4

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
)