SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #tmptbl (id int,
                      Country varchar(20),
                      name varchar(20),
                      Units int);
INSERT INTO #tmptbl
VALUES (1000, 'Canada', 'Andy', 1),
       (1000, 'Canada', 'Andy', 2),
       (1000, 'USA', 'Andy', 3),
       (1000, 'China', 'Bob', 3),
       (2000, 'Canada', 'Chase', 2),
       (2000, 'Canada', 'David', 3),
       (2000, 'USA', 'David', 4),
       (3000, 'Canada', 'John', 1),
       (3000, 'USA', 'John', 1),
       (3000, 'China', 'Dave', 3);
10 rows affected

WITH Totals AS
    (SELECT tt.ID,
            tt.Country,
            tt.Name,
            tt.Units,
            SUM(tt.Units) OVER (PARTITION BY tt.ID, tt.Name, tt.Country) AS TotalUnits
     FROM #tmptbl tt),
Ranks AS
    (SELECT T.ID,
            T.Country,
            T.Name,
            T.Units,
            T.TotalUnits,
            DENSE_RANK() OVER (PARTITION BY ID, Name ORDER BY T.TotalUnits DESC, T.Country ASC) AS DenseRank
     FROM Totals T)
SELECT R.ID,
       R.Country,
       R.Name,
       R.Units
FROM Ranks R
WHERE R.DenseRank = 1
ORDER BY ID,
         Name,
         Units;


ID
Country
Name
Units


1000
Canada
Andy
1

1000
Canada
Andy
2

1000
China
Bob
3

2000
Canada
Chase
2

2000
USA
David
4

3000
China
Dave
3

3000
Canada
John
1


DROP TABLE #tmptbl;

ID	Country	Name	Units
1000	Canada	Andy	1
1000	Canada	Andy	2
1000	China	Bob	3
2000	Canada	Chase	2
2000	USA	David	4
3000	China	Dave	3
3000	Canada	John	1