Post reply

Selecting only 2 latest records

  • September 28, 2005 at 1:00 am

    #165348

    Alright SQL guru's

    I'm sure this is easy, but my head is not with me so please help...

    I got two tables. One table with all my movies and one table with comments relating to each movies.

    I want to select all movies, but only the last two comments. My comment table has a updated field with unix timestamp so its easy to find the 2 newest comments, but i can't get all the movies with only the lastest 2 comments...i only get all comments...

    Please give me some clever input here because i'm just stuck

    Thanks

  • September 28, 2005 at 2:01 am

    #593559

    You cannot display the movie only with timestamp only, because it will result all movie. I think you must define the time when the movie being display for last time, i mean you must have a information about the displaying movie for the last time (every movie).

    If you just only compare with current time only, of course the query result will display all movie with 2 new comment (not the movie with new 2 comment)

  • September 28, 2005 at 2:07 am

    #593561

    Select [fields] FROM Movies INNER JOIN Comments ON [join clause]

    WHERE CommentID IN

     (SELECT TOP 2 CommentID From Comments c Where Comments.MovieID=Movies.MovieID ORDER BY TimeStamp DESC)

    Should work. You'll have to change the column and table names. I just guessed them.

    I tried it on a similar set of tables that I've got and it looks fine. If not, please post the table definitions, example data and I'll take another look

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 28, 2005 at 2:18 am

    #593564

    Right, but that will result all movie, not just only movie with new 2 comment.

  • September 28, 2005 at 2:25 am

    #593565

    True. If you only want movies with comments after a certain date (and by my reading that wasn't what was required. I read that he wants all movies with the latest two comments for each) add the following to the where clause

    AND Comments.Timestamp>[date]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply