MySQL: How to get user with most wins?

Question

I have a table with the following fields: id, opp1, opp2, opp1_votes, opp2_votes.

If one of the opps have more votes than another so we consider that he won. How can I get opp with most wins from this table?

Answer 1

SELECT opp, sum(win) wins FROM ( 
    SELECT opp1 opp, CASE WHEN opp1_votes > opp2_votes THEN 1 ELSE 0 END win
      FROM otable
    UNION ALL
    SELECT opp2 opp, CASE WHEN opp2_votes > opp1_votes THEN 1 ELSE 0 END win
      FROM otable
) A
GROUP BY opp
ORDER BY sum(win) DESC LIMIT 1

Answer 2

This query will find the number of wins for each user, and order them in a descending order (so the one with most wins will be the first row):

SELECT winner, COUNT(*)
FROM
    (
    SELECT opp1 AS winner FROM table
    WHERE opp1_votes > opp2_votes
    UNION ALL
    SELECT opp2 AS winner FROM table
    WHERE opp2_votes > opp1_votes
    )
GROUP BY winner
ORDER BY COUNT(*) DESC

Explanation of the query:

1. First, we find all the rows for which opp1 was the winner. We generate a table where the opp1 winners are listed under the field name "winner".
2. We do the same as in (1), but now for cases where opp2 was the winner.
3. We UNION the two table and receive a list of all winners in all games.
4. Now, we group the resulting table by winner ID, and select the count out from this grouping, resulting in a list of winners, and for each one the number of times he/she won.
5. The last step is ordering by by the number of wins, descending.

Notes:

• According to the query above, if there's a tie - there's no winner. If you want to treat ties as wins for one of the users, you'll need to define a tie-breaking heuristic.
• If you really only want a single result, you can start with SELECT TOP 1 winner, ... or finish with LIMIT 1, but as far as I know it will not save computation time in the general case, so I'll leave it.

Answer 3

Does this do what you want it to?

select top 1 opp1, 
   (select count(*) 
      from table i 
      where i.opp1 = o.opp1 and i.opp1votes > i.opp2votes) as wins 
from table o 
order desc by wins

Answer 4

SELECT
    CASE WHEN opp1_votes > opp2_votes THEN opp1 ELSE opp2 END as winner,
    COUNT(*)
FROM table
GROUP BY CASE WHEN opp1_votes > opp2_votes THEN opp1 ELSE opp2 END
ORDER BY COUNT(*) DESC

Answer 5

This little query will get you all the winners and the number of times he's won in descending order:

SELECT
(CASE WHEN `opp1_votes`>`opp2_votes` THEN `opp1` ELSE `opp2` END) `winner`,
COUNT(*) AS `no_of_times`
FROM `matches`
GROUP BY `winner`
ORDER BY `no_of_times` DESC

What we are doing here is:

1. compare find out which one is greater from opp1_votes and opp2_votes in a CASE WHEN clause
2. If opp1_votes is higher then we output opp1 in the winner column and vice versa
3. Then we group the results by winner
4. Now if count the number of rows each winner has we get the number wins by each winner
5. The the results are ordered by no_of_times won

If you want just the person with most number of wins you can add LIMIT 1 to the end of the query or you can wrap the above query and select max(no_of_times) from it like below:

SELECT `winner`, MAX(`no_of_times`) AS `no_of_times` FROM (
SELECT
(CASE WHEN `opp1_votes`>`opp2_votes` THEN `opp1` ELSE `opp2` END) `winner`,
COUNT(*) AS `no_of_times`
FROM `matches`
GROUP BY `winner`
ORDER BY `no_of_times` DESC
) AS `winners`