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,
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