CODEWITHSUNDEEP
mysqlgroup-by
Chris Mann
Chris Mann

Reputation: 77

MySQL GROUP BY - Limit to first X records

I've had a really good look around the web and it would appear plenty of people have had similar issues, but I can't seem to find a definitive resolution to my problem.

To give a bit of background information, I'm running a sports website and have a database full of past results - spanning thousands of records.

Below is some example data - (Table Name: matches)

| match_id | manager_id | outcome |
| -------- | ---------- | ------- |
|        1 |          1 |       W |
|        2 |          1 |       D |
|        3 |          1 |       D |
|        4 |          2 |       L |
|        5 |          2 |       L |
|        6 |          2 |       W |
|        7 |          2 |       D |
|        8 |          2 |       L |
|        9 |          2 |       L |
|       10 |          3 |       W |
|       11 |          3 |       W |
|       12 |          3 |       W |
|       13 |          3 |       W |
|       14 |          3 |       D |
|       15 |          3 |       D |
|       16 |          4 |       L |
|       17 |          4 |       L |
|       18 |          4 |       D |
|       19 |          5 |       W |
|       20 |          5 |       W |

What I want to do is to count the number of matches per manager, along with the outcome - which I have achieved by using this query.

SELECT
`manager_id`,
COUNT(*) AS `played`,
SUM(`outcome` = 'W') AS `won`,
SUM(`outcome` = 'D') AS `drawn`,
SUM(`outcome` = 'L') AS `lost`,
ROUND((SUM(`outcome` = 'W')/COUNT(*)) * 100, 0) AS `win_percentage`
FROM
`matches`
GROUP BY `manager_id`

Query Result

| manager_id | played | won | drawn | lost | win_percentage |
| ---------- | ------ | --- | ----- | ---- | -------------- |
|          1 |      3 |   1 |     2 |    0 |             33 |
|          2 |      6 |   1 |     1 |    4 |             16 |
|          3 |      6 |   4 |     2 |    0 |             66 |
|          4 |      3 |   0 |     1 |    2 |              0 |
|          5 |      2 |   2 |     0 |    0 |            100 |

That's all well and good, as well as being relatively simple to do.

What I want to achieve, however, is to find out the exact same data based upon the first X number of records per manager_id.

For example, say I want the above data for the first two matches of each manager. I should end up with a result as follows.

| manager_id | played | won | drawn | lost | win_percentage |
| ---------- | ------ | --- | ----- | ---- | -------------- |
|          1 |      2 |   1 |     1 |    0 |             50 |
|          2 |      2 |   0 |     0 |    2 |              0 |
|          3 |      2 |   2 |     0 |    0 |            100 |
|          4 |      2 |   0 |     0 |    2 |              0 |
|          5 |      2 |   2 |     0 |    0 |            100 |

The above is only an example as, in reality, we'll be searching for the first 50 or 100 matches per group.

Any support I can received here would be very much appreciated.

Upvotes: 3

Views: 361

Answers (1)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

Since there are no window functions in MySql you can use variables as described in this post.

set @howmany := 2;

SELECT
  manager_id,
  COUNT(*) AS played,
  SUM(outcome = 'W') AS won,
  SUM(outcome = 'D') AS drawn,
  SUM(outcome = 'L') AS lost,
  ROUND((SUM(outcome = 'W')/COUNT(*)) * 100, 0) AS win_percentage
FROM
  (SELECT 
     match_id, 
     manager_id, 
     outcome, 
    CASE WHEN manager_id != @manager THEN @row := 1 
      ELSE @row := @row + 1 END as rownum,
    CASE WHEN manager_id != @manager THEN @manager := manager_id 
      ELSE @manager END as _
  FROM (SELECT * from matches ORDER BY manager_id, match_id) temp1
  JOIN (SELECT @manager := 0) temp2
  JOIN (SELECT @row := 0) temp3
  ) temp
WHERE temp.rownum <= @howmany
GROUP BY manager_id;

Upvotes: 1

Related Questions