sql - Finding MAX of SUM of sales while incorporating field from separate table

Question

I really need some help regarding max and sum for two tables. So there are two tables.

Supplies

+-------------+------+----------+
|supplier_id  | city | supplier |
+-------------+------+----------+

Orders

+----------+-------------+------------+----------+------+
| order_id | supplier_id | order_date | sale_amt | city | 
+----------+-------------+------------+----------+------+

I need to find the top supplier (maximum combined sales amount) in each city in this format:

+------+----------+----------+
| city | supplier | sale_amt |
+------+----------+----------+

The ISSUE

I can find the sum of the sales amount along with city and supplier name(supplier) fine.
But cannot derive the MAXIMUM of those sales because using (group by) for BOTH city and supplier gives me multiple suppliers per city where I need only the TOP supplier for EACH city.
If just city name was needed it wouldn't be an issue, but needing the supplier name causes this problem.

This is the query I'm using right now and what I have so far.

SELECT s.city, s.supplier, SUM(sale_amt)
FROM Orders o
LEFT JOIN Supplies s
ON o.supplier_id = s.supplier_id
GROUP BY s.city, s.supplier
ORDER BY s.city

Your help and guidance would be much appreciated.

EDIT: After trying all your solutions and hitting snags here or there, came up with my own messy code below. This definitely has holes and could be written much better.

SELECT A.city, B.supplier, A.max_sale_amt
FROM
    (
    SELECT city, MAX(tot) as max_sale_amt
    FROM (
        SELECT supplier_id, SUM(sale_amt) as tot
        FROM Orders
        GROUP BY supplier_id ) as D
        LEFT JOIN Supplies s
        ON D.supplier_id = Supplies.id
        GROUP BY city
    ) A,

    (
    SELECT s.city, s.supplier, SUM(sale_amt) as sum_amt
    FROM Orders o, Supplies s
        WHERE o.supplier_id = s.id
    GROUP BY s.supplier, s.city
    ) B
WHERE A.city= B.city AND A.max_sale_amt= B.sum_amt

question from:https://stackoverflow.com/questions/65949982/finding-max-of-sum-of-sales-while-incorporating-field-from-separate-table

Answer 1

You may use ROW_NUMBER here:

SELECT TOP 1 WITH TIES s.city, s.supplier, SUM(sale_amt)
FROM Orders o
LEFT JOIN Supplies s ON o.supplier_id = s.supplier_id
GROUP BY s.city, s.supplier
ORDER BY ROW_NUMBER() OVER (PARTITION BY s.city ORDER BY SUM(sale_amt) DESC);