In MySQL, can I have a table returning the ten last rated games by rating?

Question

The actual question is a little more complex than that, so here goes.

I have a website which reviews games. Ratings/reviews are posted for each game, and so I have a MySQL database to handle it all.

Thing is, I'd really like a page that showed what score (out of 10) meant what, and to illustrate it would have the game that was last reviewed as an example. I can always do it without, but this would be cooler.

So the query should return something like this (but running from 10 to 0):

|---------------*----------------*-----------------*-----------------|
* game.gameName | game.gameImage | review.ourScore | review.postedOn *
|---------------*----------------*-----------------*-----------------|
| Top Game      | img            | 10              | (unix timestamp)|
| NearlyTop Game| img            | 9               | (unix timestamp)|
| Great Game    | img            | 8               | (unix timestamp)|
|---------------*----------------*-----------------*-----------------|

The information is in two tables, game and review. I think you'd use MAX() to find out the last timestamp and corresponding game information, but as far as complex queries go, I'm in way over my head.

Of course this could be done with 10 simple SELECTs but I'm sure there must be a way to do this in one query.

Thanks for any help.

Answer 1

SELECT DISTINCT game.gameName, game.gameImage, review.ourScore FROM game
LEFT JOIN review
ON game.ID = review.gameID
ORDER BY review.postedOn
LIMIT 10

Or something like that, check out how to use the Distinct first, I'm not sure on the syntax, and you may have to tell the ORDER BY DESC or ASC depending on what you want.

Answer 2

Well..

SELECT game.gameName, game.gameImage, review.ourScore
FROM game
LEFT JOIN review ON game.gameID = review.gameID
GROUP BY review.ourScore DESC 
LIMIT 10

returns a list of games grouped by each individual score. But this isn't what I want, I want the game that is last posted - this is why the timestamp is important. With that query, MySQL returns the first result it can find.

Answer 3

I think this would work:

select g.gameName, g.gameImage, r.ourScore, r.postedOn
from game g, review r
where g.gameId = r.gameId 
and r.postedOn = (select max(sr.postedOn) 
                  from review sr where sr.ourScore = r.ourScore)
group by r.ourScore
order by r.ourScore desc;

Edit: above SQL was corrected after David Grayson's comment. I think this query is pretty easy to understand but probably performs poorly compared with his solution.

Answer 4

Here is an ugly solution I found:

This query simply gets the IDs and scores of the reviews that you want to look at. I have included it so that you can understand what the trick is, without getting distracted by other stuff:

SELECT * FROM
(SELECT reviewID, ourScore FROM review ORDER BY postedOn DESC) as `r`
GROUP BY ourScore
ORDER BY ourScore DESC;

This exploits MySQL's 'GROUP BY' behavior. When the grouping is done, if the source rows have different values for different columns, then the value of the topmost source row is used. So if you had rows in this order:

reviewId  Score
1           3
0           3
2           3

Then after you group by score, the reviewId is 1 because that row was on the top:

reviewId  Score
1           3

So we want to put the most recent review on the top before we do the group by. Since ORDERing is always dones after grouping, in a single SELECT statement, I had to make a subquery to accomplish this. Now we just dress up this query a little bit to get all the fields you wanted:

SELECT `r`.*, game.gameName, game.gameImage FROM
(SELECT reviewID, ourScore, postedOn, gameID FROM review ORDER BY **postedOn DESC**) as `r`
JOIN game ON `r`.gameID = game.gameID
GROUP BY ourScore
ORDER BY ourScore DESC;

That should work.