SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    It's like skipping some rows or something.

    Code MySQL:
    SELECT DISTINCT(games.game), games.system, games.boximage FROM games
    INNER
    JOIN
     videos
     ON videos.GTitle = games.game AND videos.system = games.system
     ORDER BY videos.ID DESC LIMIT 6

    I want it to return games with the newest videos. It works however for some reason lately the newest videos I add don't affect the results from this query. I have tried many different things including both DISTINCT, and GROUP BY all of which returned the same wrong results.

  2. #2
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, DISTINCT is not a function and it applies to all listed fields.

    Secondly, using a surrogate value for chronological ordering isn't a good idea. Add an appropriate date field.

  3. #3
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The newest videos have the largest ID's, yet it is skipping by them for some reason. How can I make it work without adding anything like a date field.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jetnine View Post
    The newest videos have the largest ID's
    this might not always be the case, so please take note of the fact that we told you so
    Code:
    SELECT games.game
         , games.system
         , games.boximage 
         , videos.ID
      FROM games
    INNER
      JOIN videos
        ON videos.GTitle = games.game 
       AND videos.system = games.system
     ORDER 
        BY videos.ID DESC LIMIT 6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works but shows 6 of the same game as the 6 newest videos are all for the same game. But if I add "GROUP BY games.game" it then shows the wrong results.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT games.game
         , games.system
         , games.boximage 
         , MAX(videos.ID) as latest_video_for_this_game
      FROM games
    INNER
      JOIN videos
        ON videos.GTitle = games.game 
       AND videos.system = games.system
    GROUP
        BY games.game
    ORDER 
        BY latest_video_for_this_game DESC LIMIT 6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! That works!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •