ROW_NUMBER() OVER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ROW_NUMBER() OVER

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-06 : 07:43:21

Hi guys

I have a row number question.

What i am trying to do, is say that if the salesbanding is between 2m-5m then put the RMSC as R15

I then want to order Sales_Annualised desc in between the 2m-5m, and every odd row assign RMSC as R16

So it should be for eg
salesbanding 2-5m R16
salesbanding 2-5m R15
salesbanding 2-5m R16
salesbanding 2-5m R15

i believe i wrote the query correct, however when i have a look at it, everything thing has been assigned to R16 across all salesbanding

My query is

select ParentID,
SUM(Gross_Sales) as Sales_Annualised,
[FDMS].[dbo].[SalesBandRM](Sum(gross_sales)) as SalesBanding
Into #RM
--drop table #RM
--select * from #RM
from [FDMS].[dbo].[Dim_Outlet] o
inner join [FDMS].[dbo].[Fact_Financial_History_Annualised] fh on o.FDMSAccountNo = fh.FDMSAccountNo
group by ParentID
having SUM (fh.Gross_Sales) > 1999999
order by SUM(Gross_Sales) desc

----------------------------------------------------------------------
select
rm.ParentID,
Dba_Name,
Post_Code,
CASE
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'sPostcode',
HO,
LBG_Account,
LBG_Status,
Account_Status,
Sales_Annualised,
SalesBanding
into #test
From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID
where LBG_Status <> 'accepted'
and Account_Status ='16'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations)
and Sales_Annualised > 1999999

order by DBA_Name

--select * from #test
Drop table #test
---------------------------------------------------------------------------
Select #test.*,

case when Salesbanding in ('100m +')then 'R05'
When Salesbanding in ('50M to 100M')then 'R12'
When Salesbanding in ('2M to 5M')then 'R15'
else RMSC end as RMSC
into #RmUpdate
from #test inner join [FDMS].[dbo].[Geo_PCA_Sellers] g on g.PCA = #test.sPostcode

--drop table #RmUpdate
------------------------------------------------
--drop table #RmUpdate
--Select * from #RmUpdate

update #RmUpdate
set RMSC = case when rn % 2 = 0 then 'R15' else 'R16' end
from
(select RMSC, rn = row_number() over (order by Sales_Annualised desc)
from #RmUpdate
where RMSC = 'R15'
) RmUpdate

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 08:05:15

Try update RmUpdate instead of update #RmUpdate

update RmUpdate
set RMSC = case when rn % 2 = 0 then 'R15' else 'R16' end
from
(select RMSC, rn = row_number() over (order by Sales_Annualised desc)
from #RmUpdate
where RMSC = 'R15'
) RmUpdate

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-06 : 08:47:44

Hi Webfred
Thank you for your answer, i was so close, but yet again so far away

Why does # make a difference when updating the table ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 08:55:34

It is because your update must belong to the derived table i.e. (select ... from ...) RmUpdate
and the derived table itself is based on #RmUpdate.

I don't know how to make it more clear because english isn't my native language.

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 09:21:44

Just feel free to post your question (best with table structure and sample data and wanted result).
Someone here will have a solution for you...

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 09:22:53

Argh - the post I replied to is removed now...

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-06 : 09:31:31

HI webfred

I removed the post as i solved it my self .

Thank you very much, and hope all is well in germany

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 09:34:53

Ah - ok

Yes all is well - this evening I will go to the cinema with my colleagues: expendables II

Too old to Rock'n'Roll too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-06 : 11:43:24

can you still get a GLASS of beer in the theater? You could when i was there (1985 or so)

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 11:51:14

a bottle... a Glass I don't know but I don't thinik so.

Last visit in cinema in 1985???? --> American Ninja, Red Sonja, Commando, Gotcha, Teen Wolf...

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources