Top game with highest sales for each year

I'm using SQL Server with a table about video game sales from the years (1977-2020). The query below is what I've been using to solve the problem: finding the #1 game with the highest sales for each year. When executed the output gives me multiple of the same years and video games that are 10yrs ahead apart from where they should be.

SELECT VG1.Name, VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) AS Total_Sales
FROM Video_Game_Sales VG1
INNER JOIN Video_Game_Sales VG2 ON VG1.Name = VG2.Name
INNER JOIN  Video_Game_Sales VG3 ON VG2.Global_sales = VG3.Global_sales
INNER JOIN Video_Game_Sales VG4 ON VG3.Release_Date = VG4.Release_Date
GROUP BY VG4.Release_Date, VG1.Name
ORDER BY VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) DESC;

I want only one game for each year between 1977-2020 with the highest total sold.

Desired results:

Release_year	Title	Total_Sales
1977	Combat	1.25
1978	Space Invaders	2.53
1980	Asteroids	4.31

Solution

Assuming there is no duplicate records for each game (otherwise, you'll need to handle this first), the first step is to get the top selling games for each year (we'll filter the desired years on this subquery). Then we join this result back with the original table, in order to get the Name of the game.

Query:

SELECT
  top_sellers.Release_Year,
  sales.Name AS Title,
  top_sellers.Global_sales AS Total_Sales
FROM (
  SELECT YEAR(Release_Date) AS Release_Year, MAX(Global_sales) AS Global_sales
  FROM Video_Game_Sales
  WHERE YEAR(Release_Date) BETWEEN '1977' AND '2020'
  GROUP BY YEAR(Release_Date)
) AS top_sellers
JOIN Video_Game_Sales AS sales
ON top_sellers.Global_sales = sales.Global_sales
AND top_sellers.Release_Year = YEAR(sales.Release_Date)
ORDER by Release_Year ASC

Input:

Name	Release_Date	Global_sales
Red Dead Redemption 2	2018-10-26	50000000
Super Smash Bros. Ultimate	2018-12-7	30440000
Call of Duty: Modern Warfare	2019-10-25	30000000
Pokémon Sword / Shield	2019-11-15	25680000
Animal Crossing: New Horizons	2020-03-20	41590000

Output:

Release_Year	Title	Total_Sales
2018	Red Dead Redemption 2	50000000
2019	Call of Duty: Modern Warfare	30000000
2020	Animal Crossing: New Horizons	41590000

Demo in sqlfiddle.