codefossa Posted October 10, 2011 Share Posted October 10, 2011 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. Link to comment https://forums.phpfreaks.com/topic/248826-distinct-one-of-multiple-columns-order/ Share on other sites More sharing options...
codefossa Posted October 10, 2011 Author Share Posted October 10, 2011 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 Link to comment https://forums.phpfreaks.com/topic/248826-distinct-one-of-multiple-columns-order/#findComment-1277868 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.