SQL Query: Get Last 5 matches of all the teams out of the Fixtures table

1

Hi all,

I'm
very new to SQL and MS-Access. But My hobby is driving me to
databsetools :slight_smile: I collect every soccer game around europe but my
excelsheet can't handle my formulas anymore. So that's why I want to use
an access db with some queries.

This is my table:

Name: GamesTable

Columns:
ID
Date
Team
GoalsScored
GoalsAgainst

But now I want to retrieve a list of every team's last 5 matches. So I assume something with distinct date limit 5 ???

Can someone help my out with this one! A lifetime of gratitude will follow :smiley:

2

The table you show has one column for "Team" there needs to be a table of teams that is associated with this table to show both teams that was you could do a top 5 by date for each team.

3

And If I make a 2nd table Teams with columns:

ID
TeamName

What would the query look like to get last 5 matches??

4 
SELECT
 ID
,Date
,Team
,GoalsScored
,GoalsAgainst
FROM 
(SELECT
ID
,Date
,Team
,GoalsScored
,GoalsAgainst 
,ROW_NUMBER OVER(PARTITION BY Team ORDER BY Date DESC) RowNum
FROM Games
) G
WHERE RowNum<=5;

BTW Date is a reserved word, a data type and shouldn't be used as a column name.