October 5, 2012 at 9:57 am
I think that I'm having a end of the week, mind meltdown kind of moments. I have a table that has 3 keys
[ACCOUNTKEY] [int] NOT NULL,
[ACCOUNTSERVICEKEY] [int] IDENTITY(1000,1) NOT NULL,
[SERVICEOPTIONSKEY] [int] NOT NULL
Basically this types ties an account to a service. The problem that I found is, in the data conversion the contractor that I had working for me converted some services multiple times. So, I might have 4 records that look like this:
ACCOUNTKEY, ACCOUNTSERVICEKEY, SERVICEOPTIONSKEY
1000 1050 1016
1000 1051 1020
1000 1052 1025
1000 1053 1025
As you will noticed, I have accountkey 1000 tied to 4 services; however, 1 of those services is duplicated twice. I'm trying to write a script to leave only one of the duplicated service. So in this case, I would only want to leave accountservicekey 1052 and delete accountservicekey 1053. I've tried rank, but I can't figure out for the life of me how to only select the two duplicated services and then rank those, so that I delete all accountservicekeys with a rank > '1'. Any suggestions?
Thanks,
Jordon
October 5, 2012 at 10:10 am
This should work based on your example.
create table #SomeTable
(
[ACCOUNTKEY] [int] NOT NULL,
[ACCOUNTSERVICEKEY] [int] NOT NULL,
[SERVICEOPTIONSKEY] [int] NOT NULL
)
insert #SomeTable
select 1000, 1050, 1016 union all
select 1000, 1051, 1020 union all
select 1000, 1052, 1025 union all
select 1000, 1053, 1025
select * from #SomeTable
;with cte as
(
select *, ROW_NUMBER() over(Partition by ACCOUNTKEY, SERVICEOPTIONSKEY order by ACCOUNTSERVICEKEY) as RowNum
from #SomeTable
)
delete cte where RowNum > 1
select * from #SomeTable
drop table #SomeTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 10:18 am
Thank you so much! That worked perfectly!!!!
October 5, 2012 at 10:23 am
You're welcome. Glad that worked and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply