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. Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.