colic0 colic0 - 1 year ago 64
MySQL Question

Return last 5 results based on date and group

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
FROM TBL_ALL_English_Results t2
WHERE t2.Home_Team = t1.Home_Team

Answer Source

Consider using a correlated count aggregate query that calculates a rank order of records by DATE and 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.

FROM TBL_ALL_English_Results t1 
   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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download