common table expression order by

I want to have my column rn2 show desc order
but trying to keep the same order of the rest of my rows.

SET @myrank  = (SELECT COUNT(*) FROM dbo.program
            join dbo.programstatus s                         ON p.Program = s.program
            join dbo.Department d             ON s.Department = d.Department

 WHERE  CP.GroupType = 0
And d.Department = '1'



;with ViewPromoProducts AS
(
 select
    *, row_number()
           over(partition by P.Grouper order by @myrank desc)AS rn2  
,ROW_NUMBER() over (order by @myrank) as totrow      
  FROM  dbo.program p
          join dbo.Department d                                     ON s.Department = d.Department
              WHERE
CP.GroupType = 0  
And d.Department = '1'
         
  )
, t
as
(
select *
  , CASE rn2
       WHEN 1
          THEN convert(varchar(20), LoyaltyPrice)
       ELSE '' end as LoyaltyPrice3
         Else '0'
,(SELECT COUNT(*)  FROM  dbo.program p
        join dbo.Department d                                     ON s.Department = d.Department
         WHERE
     AND CP.GroupType = 0
And d.Department = '1') As to3
from ViewPromoProducts
)
select *
  --  , coalesce(nullif(b.LoyaltyPrice3, '0'), coalesce(a.LoyaltyPrice3, '0')) as LoyaltyPrice3
from t as a
 -- join t as b on  b.rn2 = a.rn2
End


example pic below.
so instead of rn2
1
2
3
4
5
i need
5
4
3
2
1
for only this one column.
ScreenHunter-01-Mar.-29-16.53.gif
LVL 9
Seven priceFull StackAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
put "order by rn2 desc" at the end

select *
from t as a
order by rn2 desc
End
0
HainKurtSr. System AnalystCommented:
"for only this one column."
I could not get what this mean
0
Seven priceFull StackAuthor Commented:
HainKurt: >> i try that but it now throws off my  order of my products with column name grouper.
If I take out the order by at the end then everything except the rn2 displays correct.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HainKurtSr. System AnalystCommented:
maybe you want this

over(partition by P.Grouper order by @myrank desc)AS rn2  

-->

over(partition by P.Grouper order by @myrank)AS rn2  

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brendt HessSenior DBACommented:
You should be able to change the statement:

row_number()
           over(partition by P.Grouper order by @myrank desc)AS rn2  

to

row_number()
           over(partition by P.Grouper order by @myrank ASC) AS rn2  
0
cyberkiwiCommented:
This part is ridiculous

           over(partition by P.Grouper order by @myrank desc) AS rn2  

Because as far as the order by is concerned, @myrank is a fixed value for EVERY single row, so it is as good as not having an order by.  The ordering will be random based on some secondary column.  Imagine you had a table where a column X contains only the value 11.  "Order by X" results in..?

As for sorting the rn2, once you fix your order by clauses, the ORDER BY needs to be in the final select OUTSIDE the CTE

...
)
select *
  --  , coalesce(nullif(b.LoyaltyPrice3, '0'), coalesce(a.LoyaltyPrice3, '0')) as LoyaltyPrice3
from t as a
 -- join t as b on  b.rn2 = a.rn2 
order by rn2 DESC
End -- proc

Open in new window

0
HainKurtSr. System AnalystCommented:
oops I did not notice that @ :) I thought it is a column in table

row_number() over(partition by P.Grouper order by some_Column_in_table ASC) AS rn2  
0
Seven priceFull StackAuthor Commented:
tks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.