MYSQL DB SELECT WITH MIN COUNT

Question

I got a dataset as a table it looks something like this :

###################################
UID    |TID     |MID     |RESULT  |
1      |1       |1       |1       |
2      |1       |1       |2       |
3      |1       |1       |3       |
4      |2       |1       |4       |
5      |2       |1       |5       |
6      |2       |1       |6       |
1      |1       |2       |6       |
2      |1       |2       |5       |
3      |1       |2       |4       |
4      |2       |2       |3       |
5      |2       |2       |2       |
6      |2       |2       |1       |
1      |1       |3       |2       |
2      |1       |3       |4       |
3      |1       |3       |6       |
4      |2       |3       |5       |
5      |2       |3       |3       |
6      |2       |3       |1       |

I now want to have the minimum amount of each row, grouped by TID and MID

If I do something like this:

SELECT UID, TID, MID, RESULT  
FROM matches 
WHERE matches.id = '$ID'  
ORDER BY TID, MID, RESULT

I get the things ordered in the right way. Like this:

###################################
UID    |TID     |MID     |RESULT  |
1      |1       |1       |1       |
2      |1       |1       |2       |
3      |1       |1       |3       |

3      |1       |2       |4       |
2      |1       |2       |5       |
1      |1       |2       |6       |

1      |1       |3       |2       |
2      |1       |3       |4       |
3      |1       |3       |6       |

4      |2       |1       |4       |
5      |2       |1       |5       |
6      |2       |1       |6       |

6      |2       |2       |1       |
5      |2       |2       |2       |
4      |2       |2       |3       |

6      |2       |3       |1       |
5      |2       |3       |3       |
4      |2       |3       |5       |

And I now just want to have the first row of each block. To count how often each UID is best in each block.

I don't know how to get it without doing many loop in php. If I do GROUP BY TID, MID with MIN(RESULT) it seams a bit random which UID is coming out.

SOLUTION:

SELECT COUNT(*) AS AMOUNT, t1.UID AS UID, t1.TID AS TID, t1.MID AS MID, t1.RESULT AS result
  FROM
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, um.q_result AS RESULT
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    ORDER BY TID, MID
  ) t1
  INNER JOIN
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, min(um.q_result) AS min_result
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    GROUP BY TID, MID
  ) t2 ON t2.TID = t1.TID AND t2.MID = t1.MID AND t2.min_result = t1.result
 GROUP BY t1.UID ORDER BY AMOUNT DESC

`select distinct`and the rest of your code, should do the job. — M.K, Feb 23 '18 at 12:59
just SELECT DISTINCT and then changing nothing doesn't change anything. :( — freak228, Feb 23 '18 at 13:05
What would you like to happen if you get draws - or don't you care? or will it never happen? — P.Salmon, Feb 23 '18 at 13:11
Draws can't happen. There is always one with the lowest Result. — freak228, Feb 23 '18 at 13:17
This is a very common question. The `SQL select only rows with max value on a column` question (2nd duplicate link) provides several, lot more detailed description how you can achieve the same question for maximum values. If you understand the logic from those answers, then those can be easily adapted for minimum values. — Shadow, Feb 23 '18 at 13:28

score 1 · Accepted Answer · edited Feb 23 '18 at 14:10

1

You could use subquery with the min for column result group by TID and MID

  SELECT UID, TID, MID, RESULT  
  FROM matches  m 
  inner join (
    select TID, MID, min(result) min_result
    from matches 
    group by TID, MID 
  ) t on t.TID = m.TID and t.MID = m.MID and t.min_result = m.result

Looking to your code and sample you have a wrong user_id in subselect, you have not group by but order by so i think you should change your code this way

SELECT COUNT(*) AS AMOUNT, t1.UID AS UID, t1.TID AS TID, t1.MID AS MID, t1.RESULT AS result
  FROM
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, um.q_result AS RESULT
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    ORDER BY TID, MID
  ) t1
  INNER JOIN
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, min(um.q_result) AS min_result
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    GROUP BY TID, MID
  ) t2 ON t2.TID = t1.TID AND t2.MID = t1.MID AND t2.min_result = t1.result
 GROUP BY t1.UID ORDER BY AMOUNT DESC

edited Feb 23 '18 at 14:10

freak228

33
8

answered Feb 23 '18 at 13:01

ScaisEdge

131,976
10
91
107

Thanks for your answer! The thing is just a bit more complicated. I tried to adopt it but don't get it. – freak228 Feb 23 '18 at 13:22
@freak228 answer updated .. with a suggestion ..let me know – ScaisEdge Feb 23 '18 at 13:34
still doesn't work. First there was a problem in the last ORDER BY section (so i changed it to: ORDER BY UID, TID, MID) then the error disappeared. Now it says RESULT is unknown in ON clause. – freak228 Feb 23 '18 at 13:48
asnwer udpated .. – ScaisEdge Feb 23 '18 at 13:57
Still doesn't work, I think i'll just try it with a loop. But thanks anyway! – freak228 Feb 23 '18 at 14:03
Updated your answer! It works now! THANKS! – freak228 Feb 23 '18 at 14:13
Ok ..many thanks .. you too – ScaisEdge Feb 23 '18 at 14:15

MYSQL DB SELECT WITH MIN COUNT

1 Answers1