June 23, 2014 at 1:33 pm
I have a query that is bringing back something like this.
if there is only one row for a given city they i want to delete it no mater what the row number is.
however if there is more then on row number for a given city i want to keep only the min row number and delete the others.
RowRecnumCity Action
15 City ADelete
36 City B Delete
17 City CDelete
18 City E Keep
28 City E Delete
38 City E Delete
19 City F Keep
29 City F Delete
110 City GKeep
110 City GDelete
111 City HKeep
211 City HDelete
311 City HDelete
411 City HDelete
June 23, 2014 at 1:49 pm
What have you tried?
June 23, 2014 at 1:53 pm
Do you mean to delete from the result set or the table?
June 23, 2014 at 2:02 pm
the table the data is coming from. i will be updating the datedeleted field from null to getdate()
June 23, 2014 at 2:08 pm
So something like this:
/*
1 5 City A Delete
3 6 City B Delete
1 7 City C Delete
1 8 City E Keep
2 8 City E Delete
3 8 City E Delete
1 9 City F Keep
2 9 City F Delete
1 10 City G Keep
1 10 City G Delete
1 11 City H Keep
2 11 City H Delete
3 11 City H Delete
4 11 City H Delete
*/
create table dbo.City(
CityId int identity(1,1),
CityName varchar(10),
DateDeleted datetime null
);
insert into dbo.City(CityName)
values
('City A'),
('City B'),
('City C'),
('City E'),
('City E'),
('City E'),
('City F'),
('City F'),
('City G'),
('City G'),
('City H'),
('City H'),
('City H'),
('City H');
with BaseCity as (
select
CityId,
CityName,
rn = row_number() over (partition by CityName order by CityId),
cnt = count(*) over (partition by CityName)
from
dbo.City
)
select
*
from
BaseCity
where
rn > 1 or
cnt = 1;
go
with BaseCity as (
select
CityId,
CityName,
DateDeleted,
rn = row_number() over (partition by CityName order by CityId),
cnt = count(*) over (partition by CityName)
from
dbo.City
)
update BaseCity set
DateDeleted = getdate()
where
rn > 1 or
cnt = 1;
go
select * from dbo.City;
go
drop table dbo.City;
go
June 23, 2014 at 2:12 pm
just the query to get my counts back by city.
my problem is i the record i need to keep could be say row 5 of a partition (of 7). so i would need delete 1,2,3,4,6,7 and keep row 5
June 23, 2014 at 2:42 pm
Lee Hopkins (6/23/2014)
just the query to get my counts back by city.my problem is i the record i need to keep could be say row 5 of a partition (of 7). so i would need delete 1,2,3,4,6,7 and keep row 5
Well, since I don't have the data you have I had to make do with what I set up. Take what I did and see what you need to do to make it work with your data.
If you still have problems, post the DDL and sample data (look at how I did that in my post) and we will see what we can do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply