CODEWITHSUNDEEP
sqlmysql
eyerouge
eyerouge

Reputation: 15

Make MySQL show every users rating and then sort the results using ratings?

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

Answers (1)

Quassnoi
Quassnoi

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

Related Questions