CODEWITHSUNDEEP
sqlsql-server
Mr 2017
Mr 2017

Reputation: 113

Ranking data (descending)

I've written a query which ranks products by cash sales data but does it in ascending order ie the product with the smallest sales appears first!

Do you know how I can amend the code below so it ranks the data in DESCENDING instead of ascending order?

With the Order By function, you can just put the letters DESC after Order By: 

select 
    es.ClientProductID,
    es.ProductName,
    ash.sales * ash.Price as CashSales, 
    RANK() OVER(ORDER BY ash.sales * ash.Price) as RankedDataBySales 
from AggregatedSalesHistory as ash
join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSID

where es.SubcategoryName = 'Yogurt' and ash.segmentid = 0;

TIA

Upvotes: 0

Views: 45

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

try like below

select 
    es.ClientProductID,
    es.ProductName,
    ash.sales * ash.Price as CashSales, 
    RANK() OVER(ORDER BY (ash.sales * ash.Price) desc) as RankedDataBySales 
from AggregatedSalesHistory as ash
join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSID

where es.SubcategoryName = 'Yogurt' and ash.segmentid = 0;

from your comment it seems you want top 10 rank sales so put below query

with cte as
 (
select 
        es.ClientProductID,
        es.ProductName,
        ash.sales * ash.Price as CashSales, 
        RANK() OVER(ORDER BY (ash.sales * ash.Price) desc) as RankedDataBySales 
    from AggregatedSalesHistory as ash
    join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSID

    where es.SubcategoryName = 'Yogurt' and ash.segmentid = 0;
) select * from cte where RankedDataBySales>=10

Upvotes: 1

ssy
ssy

Reputation: 1

select 
    es.ClientProductID,
    es.ProductName,
    ash.sales * ash.Price as CashSales, 
    RANK() OVER(ORDER BY ash.sales * ash.Price DESC) as RankedDataBySales 
from AggregatedSalesHistory as ash
join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSID
where es.SubcategoryName = 'Yogurt' and ash.segmentid = 0;

Upvotes: 0

Related Questions