I have tried my best to google this over the past few days but I cannot find the solution that I need for MS Access or MySQL
I have a table of soccer results where I want to pull back the last 5 results per home team based on the date.
I have tried this in access following MS advice and I cannot get it to work how it says it should. I am quite new to MySQL and got a bit closer with the below code but that only returns the last result and if I change it to 2 it will not run due to Subquery returns more than 1 row. I don't need the exact answer I am happy if someone can give me a link to follow for either Access or MySQL. Below is what I was using which did return the last result fine.
FROM TBL_ALL_English_Results t1
WHERE t1.id = (SELECT t2.id
FROM TBL_ALL_English_Results t2
WHERE t2.Home_Team = t1.Home_Team
ORDER BY t2.id DESC
Consider using a correlated count aggregate query that calculates a rank order of records by
HOME_TEAM group, then uses this rank order to filter query results. Below should be compliant in MySQL and MS Access, even SQL Server and other dialects.
SELECT t1.* FROM TBL_ALL_English_Results t1 INNER JOIN ( SELECT t2.Home_Team, t2.Date, (SELECT Count(*) FROM TBL_ALL_English_Results sub WHERE t2.Date <= sub.Date AND t2.Home_Team = sub.Home_Team) AS GameOrder FROM TBL_ALL_English_Results As t2 ) AS LastFiveDates ON t1.Date = LastFiveDates.Date AND t1.Home_Team = LastFiveDates.Home_Team WHERE LastFiveDates.GameOrder <= 5
While most databases can run this query, performance may differ especially in the file level database, MS Access, if table results are above 10,000 as opposed to the server level RDMS, MySQL.