October 14, 2009 at 9:23 am
Hello
I have a scenario given below . All the fields are duplicate except for the AccID .I need to select only one row(accountID = 759518) from these two sets of rows . Also the AccName is somewhat different . One with space (A & B)and one without space (A&B) .
AccIDAccName Rep CId StartDate Type Purchased Cost UsedBalanceBannerRevenue
759518 A&B bp 3477 2009-08-07 CPM 20000 400 762412376152.48
AccIDAccName Rep CId StartDate Type Purchased Cost UsedBalanceBannerRevenue
834582 A & B bp 3477 2009-08-07 CPM 20000 400 762412376152.48
Its urgent. Please help .
Thanks,
October 14, 2009 at 9:28 am
try this
with cteWithRow( Rep, CId ,StartDate, Type ,Purchased, Cost, Used, Balance, BannerRevenue,Rown)
as
(
Select Rep ,CId ,StartDate, Type, Purchased, Cost, Used ,Balance, BannerRevenue,
row_number() over partition by (Rep, CId, StartDate, Type ,Purchased ,Cost, Used, Balance, BannerRevenue order by Accname)
from yourtab
)
delete from cteWithRow
where Rown <> 1
October 14, 2009 at 9:33 am
I was going to ask if you had a preference about which account name to keep. The one with spaces, or the one without?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 9:34 am
Thanks for the prompt reply .
The below query is raising the following error :
with cteWithRow( RepName,CampaignID,CampaignStartDate,[Type],Purchased,Cost,Used,Balance,BannerRevenue,As_Of_Date,Rown)
as
(
Select RepName,CampaignID,CampaignStartDate,[Type],Purchased,Cost,Used,Balance,BannerRevenue,As_Of_Date,
row_number() over partition by (RepName,CampaignID,CampaignStartDate,[Type],Purchased,Cost,Used,Balance,BannerRevenue,As_Of_Date )
from RPT_UsageReport
)
delete from cteWithRow
where Rown <> 1
"Incorrect syntax near 'partition"
October 14, 2009 at 9:34 am
No preference on Name .
October 14, 2009 at 9:36 am
An opening parenthesis is missing. It should read: OVER (PARTITION BY etc
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 9:42 am
Thanks a lot . That worked .
October 14, 2009 at 11:46 am
You're welcome, PSB. Could you please take another minute to explain to us why this was "urgent"? That is a word that is being used in more and more questions here, and it is pointless. We are all volunteers who have deadlines on our REAL jobs. Other people's questions are just as urgent to them as yours are to you. Speaking for myself only, I usually deliberately take my time to answer questions marked "urgent" or ignore them completely. An online forum is not a hospital emergency ward, and writing code may be stressful, but it is not life or death. Questions sent to SSC can take hours or days to be answered.
If you REALLY want fast answers, take a few minutes to set up sample scripts with data as shown in this article[/url]. Don't just cut and paste something from the grid or a spreadsheet. If you take the time to give sample data and sample output that you would expect from a correct solution, I guarantee you that more people will tackle your problem ahead of questions that have somewhat vague verbal descriptions. Plus you will also more likely get coded and tested solutions. Finally, you will win friends among the volunteers because you are going to the effort to help them help you. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 11:55 am
One point that may or may not matter is that the deduping script used here ignores the AccName column. That handles the one with extra spaces, but you'll need to be sure that the other columns are a valid test of duplication by themselves.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply