Reputation: 15
Q: How do I make MySQL also show every users rating and then sort the results using ratings, desc?
This is all used for a gaming ladder. weblGames has the result of every reported game and has info about who won/lost, and what the winner/losers rating became (winner_elo & loser_elo).
Here is a partial screenshot of the table: http://www.ubuntu-pics.de/bild/21059/screenshot_87_RTDZBb.png
Using only this table the current MySQL code displays (thanks to this place) every players name and the number of games he played within the most recent x days, .
I want to keep that info, but also be able to output every players current Elo Point (which equals the winner_elo or loser_elo in his most recently played game.)
Here is the code that I currently have and that displays every player and his/her amount of most recent played games within x days:
SELECT userid, count(*) as cnt
FROM
(
SELECT winner as userid
from webl_games g
where (g.reported_on > now() - interval 4 day
UNION ALL
SELECT loser as userid
from webl_games g
where g.reported_on > now() - interval 4 day
) t
GROUP BY userid
HAVING COUNT(*) >= 3
Upvotes: 1
Views: 209
Reputation: 425863
SELECT userid, COUNT(*) as cnt,
(
SELECT CASE t.userid WHEN winner THEN winner_elo ELSE loser_elo END
FROM webl_games l
WHERE t.userid IN (winner, loser)
ORDER BY
reported_on DESC
LIMIT 1
) AS last_elo
FROM (
SELECT winner as userid
FROM webl_games g
WHERE (g.reported_on > now() - interval 4 day
UNION ALL
SELECT loser as userid
FROM webl_games g
WHERE g.reported_on > now() - interval 4 day
) t
GROUP BY
userid
The subquery here can be inefficient.
If it is and your table has a PRIMARY KEY
, rewrite it as this:
SELECT userid, cnt,
(
SELECT q2.userid WHEN winner THEN winner_elo ELSE loser_elo END
FROM webl_games l
WHERE l.id IN (lwin, llose)
ORDER BY
reported_on DESC
LIMIT 1
)
FROM (
SELECT userid, COUNT(*) as cnt,
(
SELECT id
FROM webl_games l
WHERE t.userid = winner
ORDER BY
reported_on DESC
LIMIT 1
) AS lwin,
(
SELECT id
FROM webl_games l
WHERE t.userid = loser
ORDER BY
reported_on DESC
LIMIT 1
) AS llose
FROM (
SELECT winner as userid
FROM webl_games g
WHERE (g.reported_on > now() - interval 4 day
UNION ALL
SELECT loser as userid
FROM webl_games g
WHERE g.reported_on > now() - interval 4 day
) t
GROUP BY
userid
) q2
Upvotes: 1