Alan Tingey Alan Tingey - 1 month ago 10
MySQL Question

MySQL: SQL Returning Just Three Results of a Field

The query below produces working results but I would like to return just the first 3 rows for each team:

mysql> select * from plain_result;
+-----------+----------------------------+----------------+----------------+--------+---------+------------+----------------+
| fixtureid | fixturedate | team | opponent | result | mstatus | team_score | opponent_score |
+-----------+----------------------------+----------------+----------------+--------+---------+------------+----------------+
| 364322 | 2016-10-02 15:30:00.000000 | Arsenal | Burnley | W | A | 1 | 0 |
| 364310 | 2016-09-24 16:30:00.000000 | Arsenal | Chelsea | W | H | 3 | 0 |
| 364295 | 2016-09-17 14:00:00.000000 | Arsenal | Hull | W | A | 4 | 1 |
| 364288 | 2016-09-10 14:00:00.000000 | Arsenal | Southampton | W | H | 2 | 1 |
| 364277 | 2016-08-27 14:00:00.000000 | Arsenal | Watford | W | A | 3 | 1 |
| 364270 | 2016-08-20 16:30:00.000000 | Arsenal | Leicester | D | A | 0 | 0 |
| 364261 | 2016-08-14 15:00:00.000000 | Arsenal | Liverpool | L | H | 3 | 4 |
| 364315 | 2016-10-01 14:00:00.000000 | Bournemouth | Watford | D | A | 2 | 2 |
| 364307 | 2016-09-24 14:00:00.000000 | Bournemouth | Everton | W | H | 1 | 0 |
| 364297 | 2016-09-17 14:00:00.000000 | Bournemouth | Man City | L | A | 0 | 4 |
| 364286 | 2016-09-10 14:00:00.000000 | Bournemouth | West Brom | W | H | 1 | 0 |
| 364276 | 2016-08-27 14:00:00.000000 | Bournemouth | Crystal Palace | D | A | 1 | 1 |
| 364272 | 2016-08-21 15:00:00.000000 | Bournemouth | West Ham | L | A | 0 | 1 |
| 364260 | 2016-08-14 12:30:00.000000 | Bournemouth | Man United | L | H | 1 | 3 |
| 364322 | 2016-10-02 15:30:00.000000 | Burnley | Arsenal | L | H | 0 | 1 |
| 364312 | 2016-09-26 19:00:00.000000 | Burnley | Watford | W | H | 2 | 0 |
| 364296 | 2016-09-17 14:00:00.000000 | Burnley | Leicester | L | A | 0 | 3 |
| 364287 | 2016-09-10 14:00:00.000000 | Burnley | Hull | D | H | 1 | 1 |
| 364275 | 2016-08-27 14:00:00.000000 | Burnley | Chelsea | L | A | 0 | 3 |
| 364266 | 2016-08-20 14:00:00.000000 | Burnley | Liverpool | W | H | 2 | 0 |
| 364257 | 2016-08-13 14:00:00.000000 | Burnley | Swansea | L | H | 0 | 1 |


Please note the plain_result is based on a view and not a table.

Many thanks, Alan.

Answer

Usually, the best way to do this in MySQL is to use variables:

select pr.*
from (select pr.*,
             (@rn := if(@t = team, @rn + 1,
                        if(@t := team, 1, 1)
                       )
             ) as seqnum
      from plain_results pr cross join
           (select @rn := 0, @t := '') params
      order by team, ??
     ) pr
where seqnum <= 3;

The ?? is for the column that defines which three you want. In all likelihood, it is probably fixturedate asc or fixturedate desc.