Get the highest value for a category from a SUM using subquery

[former_SQLA_member1695068]

Hi all, I've done a query that gives me the highest sales value for each category for each country. But I only want to know what is the category that sold the highest for each countr. This is my query that gives me those values, but without the last filter for the highest category for each country, and since I can't do a MAX (SUM(od.unitprice * od.Quntity) I was wondering if there is a Subquery I could use

Select country, categoryname, SUM(od.unitprice * od.Quantity) as Valor
    From Categories c 
    Join Products p on c.CategoryID = p.CategoryID  
    Join [Order Details] od on p.ProductID = od.ProductID
    Join Orders o on od.orderID = o.OrderID 
    Join Customers c2 on o.CustomerID = c2.CustomerID
    Group by Country, CategoryName, year(o.OrderDate)
    Having year(o.OrderDate) = 1997

[BudDurland]

It seems to me that "OUTER APPLY (select top 1 ...)" might be useful here. The sample query is ambiguous on which tables have the 'country' and 'categoryname' columns, so I didn't try to make an example.

[VolkerBarth]

I do not really understand the question, and without the schema I can also not make sample code, and a few very general hints don't seem worthwhile.

Some test data and the expected result set are helpful to get an answer.

[former_SQLA_member1695068]

Hi, thank you all for the answers, I finally did it doing a partition, the result is here:

Select * from
    (Select ROW_NUMBER() OVER(PARTITION BY COUNTRY ORDER BY SUM(od.unitprice * od.Quantity) DESC) AS 'ROW_NUMBER', country, categoryname, SUM(od.unitprice * od.Quantity) as Valor
    From Categories c 
    Join Products p on c.CategoryID = p.CategoryID  
    Join [Order Details] od on p.ProductID = od.ProductID
    Join Orders o on od.orderID = o.OrderID 
    Join Customers c2 on o.CustomerID = c2.CustomerID
    Group by Country, CategoryName, year(o.OrderDate)
    Having year(o.OrderDate) = 1997) as tbl
    Where tbl.ROW_NUMBER = 1