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

http://stackoverflow.com/questions/134958/get-top-results-for-each-group-in-oracle — dugas, Mar 30 '13 at 14:14

Gordon Linoff · Answer 1 · 2013-03-31T13:57:10.427

3

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

edited Mar 31 '13 at 13:57

answered Mar 30 '13 at 15:06

Gordon Linoff

1,242,037
58
646
786

You are correct - it is not about home or away teams, it is about the last 6 matches played by any team in the dataset. However, I'm not sure what the query is doing but it had not returned after 10 minutes when running on my dataset (the data contains results for over 400 teams stretching back a number of years) – phrenetic Mar 30 '13 at 16:30
@phrenetic . . . I made a slight change so it receives the 6 most recent. How large is the matches table? 10 minutes seems way to long for even a few thousand rows. – Gordon Linoff Mar 30 '13 at 16:33
@phrenetic . . . Trying to do this as a correlated subquery seems like a nightmare. The alternatives are the variable-counter approach and using a temporary table. I updated with the improved version. – Gordon Linoff Mar 30 '13 at 16:47
@GordonLinoff -- I think we were working on the same solution :) -- sqlfiddle.com/#!2/8bb31/8 – sgeddes Mar 30 '13 at 16:48
+1 though, you're approach is better as it doesn't rely on joining on a key. Nice job! – sgeddes Mar 30 '13 at 16:56
@phrenetic . . . the variable names were not set up correctly in the second subquery (one part used TeamCounter, another GameCounter). Perhaps with the right variable names it will work. – Gordon Linoff Mar 31 '13 at 13:57

sgeddes · Accepted Answer · 2013-03-30T16:47:24.040

2

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

edited Mar 30 '13 at 16:47

answered Mar 30 '13 at 14:09

sgeddes

62,311
6
61
83

I'm not sure this is doing what I expected as it appears to generate far more than 6 matches for each team. (There were 19 matches listed for the first team in the result set) – phrenetic Mar 30 '13 at 14:53
@phrenetic -- did you see the Fiddle? That picked 6 for each team? Can you update with some sample data producing incorrect results? – sgeddes Mar 30 '13 at 14:56
added some extra data to the fiddle. I think the query is working although the output was not quite as I envisaged. If you look at the Rangers v Celtic match, it is listed once whereas I expected it to be listed twice - once for Rangers, once for Celtic. Are you sure the query is determining the last 6 matches as opposed to the last 6 home matches? – phrenetic Mar 30 '13 at 16:26
@phrenetic -- can you share the Fiddle link where you updated the data? I think I misunderstood your question -- this was originally dependent on the home team only. Sounds like you don't care if they're home or away, just their last 6 games? Please share Fiddle and I can revise. – sgeddes Mar 30 '13 at 16:35
@phrenetic -- here's another attempt -- I'm still a little confused what to do about duplicates, but this list each team, with their last 6 games: http://sqlfiddle.com/#!2/8bb31/8 – sgeddes Mar 30 '13 at 16:46
see http://sqlfiddle.com/#!2/98de37/2 for updated data. Duplicates are ok - I will deal with them separately. Latest iteration looks good to me (just looking through in more detail) – phrenetic Mar 30 '13 at 17:28
Bizarrely this didn't work on my real MySQL database until I changed the final order by clause to "order by team, MatchDate desc" – phrenetic Mar 31 '13 at 15:45
Actually I have since discovered that it always fails on the first run through, but then works every time thereafter (on my real MySQL database) – phrenetic Apr 01 '13 at 20:16
@phrenetic -- a guess would be the prevHome user-defined variable might need to be initialized. You try adding that to the join with the teamCounter variable. – sgeddes Apr 01 '13 at 20:26

Michael Durrant · Answer 3 · 2013-03-30T15:58:02.697

0

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.

edited Mar 30 '13 at 15:58

answered Mar 30 '13 at 13:53

Michael Durrant

93,410
97
333
497

`LIMIT` inside `IN` subqueries is not allowed. – ypercubeᵀᴹ Mar 30 '13 at 13:58
Added version without `in` – Michael Durrant Mar 30 '13 at 14:04
Parenthesis is missing or some lines were not copied-pasted. – ypercubeᵀᴹ Mar 30 '13 at 14:29
Assuming I have to put a parenthesis in front of the "select HomeTeam" (in the older mysql example), this still generates an error: "Every derived table must have its own alias" – phrenetic Mar 30 '13 at 14:46
@phrenetic -- that means you need to put a table alias after the first closed parentheses (before the order by) -- Michael, I'm editing your response slightly to include. – sgeddes Mar 30 '13 at 14:54
@sgeddes -- that now generates a different error: "Unknown column 'HOME' in 'where clause'". Also, where does "home_team" (in the where clause) come from? – phrenetic Mar 30 '13 at 15:20
Changed the 2nd query quite a bit. Please see update and see if that works for you. – Michael Durrant Mar 30 '13 at 15:37
@MichaelDurrant - new error message "Subquery returns more than 1 row" – phrenetic Mar 30 '13 at 15:40
Updated to use IN. Hopefully ok for outer query. – Michael Durrant Mar 30 '13 at 15:58
Just get a message saying this version of MySQL doesn't support LIMIT and IN/ALL/ANY/SOME subquery. – phrenetic Mar 30 '13 at 16:32

Getting last 6 records for each team in a MySQL database

3 Answers3

Linked