I want to show 4 games which have the newest videos. This right now is not working as it shows just the 4 newest added games.
SELECT DISTINCT(videos.GTitle), videos.system, games.year, games.game, games.boximage FROM games
ON videos.GTitle = games.game AND videos.system = games.system
WHERE games.year>=2005 ORDER BY videos.ID DESC LIMIT 4
could you show the table layouts and a few rows of data please?
best would be mysqldump output -- CREATE TABLE statements, and a few INSERTs
i'm sorry, without sample data i don't get it
perhaps you could explain how the tables are related, whether it's one-to-many or many-to-one, and how you would determine which ones are the "newest videos"
I am trying to select 4 videos where GTitle is DISTINCT - ordered by ID to get the newest 4 videos which are all from different games.
But I want to display the 4 games, not the videos. Right now it is not displaying the correct games.
well, i still don't understand the relationship so i can summarize the approach like this --
get the max(videoID) for each game
sort by this maxID DESC and use LIMIT 4
Cool. MAX did it. It wasn't for videoid as that is something different, but I got it working thanks to you. Thanks alot! Sorry I couldn't have been more informative.
SELECT MAX(videos.ID) AS vidid, videos.GTitle, videos.system, games.year, games.game, games.boximage FROM games
ON games.game = videos.GTitle AND games.system = videos.system
WHERE games.year>=2005 GROUP BY videos.GTitle ORDER BY vidid DESC LIMIT 4