MYSQL select limit last 5 results per unique value

Question

I am currently pulling all the results from my database where the results are where season = '19'. I would like to pull the last 5 results of each teamname where the season = '19' but cannot work out how to do this.

I think it would look like the following:

Select (each teamname from footballresults  and get last 5 games) Teamname, score1, score2 from footballresults where Code = '0101' and   Season = '19' and gamedate < CURDATE()

The current code is:

Select Teamname, score1, score2 from footballresults where Code = '0101' and   Season = '19' and gamedate < CURDATE()

Currently the script will pull every result for that league in the season but essentially i would like to loop for each team and get the last 5 results for EACH team so

Currently it gets all the games for each team ( select * from DB where season = '19')

I would like to only return the last 5 games for each team so:

Select * from(
Team A Last 5 games
Team B Last 5 games
Team C Last 5 games
Team D Last 5 games
Team E Last 5 games
)

for each teamname loop through and select only last 3 games order by gamedate (The following example is if I wanted the last 3 results per team)-

+--------+---+---+--+
| Team a | 1 | 1 |  |
+--------+---+---+--+
| Team a | 4 | 1 |  |
+--------+---+---+--+
| Team a | 1 | 3 |  |
+--------+---+---+--+
| Team b | 0 | 0 |  |
+--------+---+---+--+
| Team b | 1 | 2 |  |
+--------+---+---+--+
| Team b | 2 | 0 |  |
+--------+---+---+--+

I am using MYSQL version 5.6

Possible duplicate of [Mysql - return last 3 results in table](https://stackoverflow.com/questions/3059168/mysql-return-last-3-results-in-table) — sanyam, Mar 29 '19 at 09:53
@sanyam - But how would that give my last 5 from each teamname? — Jarratt Perkins, Mar 29 '19 at 09:57
Possible duplicate of [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Nick, Mar 30 '19 at 05:10

score 0 · Answer 1 · answered Mar 29 '19 at 10:56

You can use a subquery that gets the count of more recent dates and require it to be less than 5.

SELECT fbr1.teamname,
       fbr1.score1,
       fbr1.score2
       FROM footballresults fbr1
       WHERE fbr1.code = '0101'
             AND fbr1.season = '19'
             AND fbr1.gamedate < curdate()
             AND (SELECT count(*)
                         FROM footballresults fbr2
                         WHERE fbr2.code = '0101'
                               AND fbr2.season = '19'
                               AND fbr2.gamedate < curdate()
                               AND fbr2.teamname = fbr1.teamname
                               AND fbr2.gamedate > fbr1.gamedate) < 5;

This may fail however if there can be more than one game per day. In that case you need another criteria to distinguish rows from each other like an ID. For example:

   ...
                              AND (fbr2.gamedate > fbr1.gamedate
                                    OR fbr2.gamedate > fbr1.gamedate
                                       AND fbr2.id > fbr1.id) < 5;

Hi - Thanks for your reply. I tried the above code but it still seems to get all the games rather then the last 5? — Jarratt Perkins, Mar 29 '19 at 11:30

MYSQL select limit last 5 results per unique value

1 Answers1