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?
 
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.