Distinct One of Multiple Columns + Order

[codefossa]

Okay, so what I'm trying to do is select the newest review for each movie.  The table looks like this.

 

movieidusernametimestamp

515Pirategirl1317681086

314KatieLee1316868469

413gypsygrl22821316747249

212Kira1316086506

311Pirategirl1316037234

110jon2271315739335

29Pirategirl1315541865

28critical0glitch1315507796

27KatieLee1315438818

15Ozzy1314759243

14Kira1314755101

13peacemaker50001314754319

12critical0glitch1314753505

11Pirategirl1314748843

 

Here's one of many attempts that end up returning every row.

 

SELECT `y`.`movie`, `id`, `username`, `timestamp`
FROM `reviews` `x`
RIGHT JOIN (SELECT DISTINCT `movie` FROM `reviews`) AS `y`
ON `x`.`movie` = `y`.`movie`
ORDER BY `id` DESC

 

Also, if I use GROUP, it will return the oldest reviews instead of the newest.

[codefossa]

Figured it out after a bit more trying.   Incase it will help anyone else ..

 

SELECT `movie`, MAX(`id`), `username`, `timestamp`
FROM `reviews`
GROUP BY `movie`
ORDER BY `id` DESC