Getting last 6 records for each team in a MySQL database

Question

I have a MySQL database containing soccer results and want to retrieve just a specific subset of that data.

The data consists of one table containing MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals

How can I retrieve a subset of this data that contains the last 6 matches that each team has been involved in?

Whilst I can do this for a single team, how do I get a single subset that contains the last 6 matches for each team in the table? (I am not worried that the subset may contain some duplicates).

Answer 1

Here's one way to do it with a user-defined variable:

select MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals
from (
  select 
    MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals,
    @teamCounter:=IF(@prevHome=HomeTeam,@teamCounter+1,1) teamCounter,
    @prevHome:=HomeTeam
  from yourtable
    join (select @teamCounter:=0) t
  order by HomeTeam, MatchDate desc
  ) t 
where teamCounter <= 6

SQL Fiddle Demo

And here is the update from the Fiddle:

select team, MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals
from (
  select 
    team, yourtable.MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals,
    @teamCounter:=IF(@prevHome=team,@teamCounter+1,1) teamCounter,
    @prevHome:=team
  from yourtable
    join (
      select distinct matchdate, hometeam team
      from yourtable
      union 
      select distinct matchdate, awayteam
      from yourtable
    ) allgames on yourtable.matchdate = allgames.matchdate
        and (yourtable.hometeam = allgames.team or yourtable.awayteam = allgames.team)
    join (select @teamCounter:=0) t
  order by team, yourtable.MatchDate desc
  ) t 
where teamCounter <= 6
order by team

Updated SQL Fiddle