How to make Ranking by shorting in Two different way in SQL Server?

Question

I need to do Ranking, if the value is negative, then sort it by Asc, if it is positive then sort it by Desc

select
    Itemcode,
    isnull(sum(ss.DiscQty * ss.Cost),0) DescCost,
    RANK()OVER(Partition by Itemcode order by 
        case when isnull(sum(ss.DiscQty * ss.Cost),0) < 0 THEN isnull(sum(ss.DiscrepancyQty * ss.Cost),0) END ASC,
        case when isnull(sum(ss.DiscQty * ss.Cost),0) > 0 THEN isnull(sum(ss.DiscQty * ss.Cost),0) END DESC
    ) RANKS
from
    ss
Group by
    ItemNo

Expecting Result

ItemCode    DiscQty    Rank
===========================
111         -5000       1
121         -4500       2
222          10000      3
223          3000       4

But I am getting all the Ranks are 1,

I just want to sort the DiscQty in Asc When DiscQty < 0

and DiscQty in Desc Order When `DiscQty > 0

Answer 1

You want multiple keys like this:

RANK() OVER (ORDER BY (CASE WHEN SUM(ss.DiscQty * ss.Cost) < 0 THEN SUM(ss.DiscQty * ss.Cost) ELSE 0 END) ASC,
                      SUM(ss.DiscQty * ss.Cost) DESC
) RANKS

You don't need the PARTITION BY.