Help in this query in SQL Server

Question

That consultation:

SELECT C.CompanyName, MAX(SOH.TotalDue) AS Total, A.CountryRegion FROM SalesLT.SalesOrderHeader SOH
INNER JOIN SalesLT.Customer C ON C.CustomerID = SOH.CustomerID
INNER JOIN SalesLT.CustomerAddress CA ON CA.CustomerID = C.CustomerID
INNER JOIN SalesLT.Address A ON A.AddressID = CA.AddressID
GROUP BY A.CountryRegion, C.CompanyName
ORDER BY MAX(SOH.TotalDue) DESC;

Return this list:

But what I need is only one line of the United Kingdom Maximum Total and one line of the United States Maximum Total.

I am unable to do this query select only one from each Countryregion. Someone help me?

Answer 1

How you are grouping by Companyname is normal that it breaks line. To group only by Countryregion you need to delete that column of select.

SELECT MAX(SOH.TotalDue) AS Total, A.CountryRegion FROM SalesLT.SalesOrderHeader SOH
INNER JOIN SalesLT.Customer C ON C.CustomerID = SOH.CustomerID
INNER JOIN SalesLT.CustomerAddress CA ON CA.CustomerID = C.CustomerID
INNER JOIN SalesLT.Address A ON A.AddressID = CA.AddressID
GROUP BY A.CountryRegion
ORDER BY MAX(SOH.TotalDue) DESC;