CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
phrenetic
phrenetic

Reputation: 339

Getting last 6 records for each team in a MySQL database

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).

Upvotes: 3

Views: 422

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Your question isn't specifically about home team or away team, so I assume a match could have both.

The following query will get the first six games, regardless of where they were played:

select MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals
from (select m.*,
             (select count(*)
              from matches m2
              where (m.hometeam = m2.hometeam or m.hometeam = m2.awayteam) and
                    m.matchdate <= m2.matchdate
             ) GameCounter
      from matches m
     ) m
where GameCounter <= 6

It is using a correlated subquery to get the match.

For performance, I am giving up on the idea of a correlated subquery. This is borrowing the idea from @sgeddes for counting within a group:

      select m.*
      from (select team, matchdate,
                   @teamCounter:=IF(@prevTeam=Team, @teamCounter+1,1) as teamCounter,
                   @prevTeam:=Team
            from ((select m.hometeam as team, m.*
                   from matches m
                   group by h.hometeam
                  ) union all
                  (select m.awayteam as team, m.*
                   from matches m
                   group by m.awayteam
                  )
                 ) m cross join
                 (select @teamCounter:=0) const
            group by team
            order by team, matchdate desc
           ) m
      where TeamCounter <= 6

Upvotes: 3

sgeddes
sgeddes

Reputation: 62861

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

Upvotes: 2

Michael Durrant
Michael Durrant

Reputation: 96604

I would use something like:

Using IN (newer mysql)

select *,HomeTeam AS HOME from myTable
where HomeTeam in
    (select HomeTeam from myTable 
    where Hometeam = HOME 
    order by created_date limit 6)
group by HomeTeam

Without IN (older mysql)

select * from myTable t1
where HomeTeam IN
    (select HomeTeam from myTable t2
    where t2.HomeTeam = t1.HomeTeam
        order by created_date 
        limit 6)
group by t1.HomeTeam

Note though that you should really be using and labeling with ID's here.

If not already, this table should have a primary key. Ideally called either ID or [table_name]_id. This will enable you to do subselect and joins using this ID. Never assume that the records will be unique anyway. Go with a ID auto incrementing Primary Key as a general practice and it will help you a lot.

Upvotes: 0

Related Questions