This will return the last five games for each team.

If you want to return the last five games for a single team, you can have a simpler approach (or approaches).

DECLARE @Team int;

SET @Team = 1; --Define the team

SELECT TOP (5) --Return only 5 rows

g.GameID,

g.GameDate,

t.TeamName,

g.GoalsScored,

g.GoalsAgainst

FROM Games g

JOIN Teams t ON g.TeamID = t.TeamID

WHERE g.TeamID = @Team --Filter the team

ORDER BY g.GameDate DESC; --Define the order to get consistent results

--This will work on SQL Server 2012 and more recent

SELECT

g.GameID,

g.GameDate,

t.TeamName,

g.GoalsScored,

g.GoalsAgainst

FROM Games g

JOIN Teams t ON g.TeamID = t.TeamID

WHERE g.TeamID = @Team --Filter the team

ORDER BY g.GameDate DESC --Define the order to get consistent results

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY; --Return only 5 rows

Reference: https://msdn.microsoft.com/en-us/library/ms188385.aspx