Find most profitable brand by year as a measure of gross profit

Question

Desired output:

Year	brand	gross_profit
2015	x	1000
2016	y	2000
2017	y	1500
2018	x	2000

However, with the following code, I am yielding highest profit per brand per year. What am I doing incorrectly?

--Find most profitable brand for each year (measured by gross profit)
SELECT 
    DISTINCT Year, 
    brand,
    MAX(gross_profit) AS gross_profit
FROM dbo.Data$
GROUP BY brand, Year
ORDER BY YEAR ASC;

Answer 1

We can use a sub-query to find the highest gross-profit each year.

create table data(
Year int,
brand varchar(10),
gross_profit int);
insert into data values
(2015,'x',1000),
(2016,'y',2000),
(2017,'y',1500),
(2018,'x',2000),
(2015,'y',800),
(2016,'x',1750),
(2017,'x',1000),
(2018,'y',1500);

SELECT 
    d.Year, 
    d.brand,
    d.gross_profit
FROM Data d
JOIN ( SELECT Year y,
       MAX(gross_profit) mgp
       FROM Data
       GROUP BY Year) ym
ON Year = y 
  AND gross_profit = mgp
ORDER BY YEAR ASC;

Year | brand | gross_profit
---: | :---- | -----------:
2015 | x     |         1000
2016 | y     |         2000
2017 | y     |         1500
2018 | x     |         2000

db<>fiddle here