Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Select Top 10 by Date

    Hi all - Can anyone help me out here please?
    I have a 'news' article table in my database.
    It has a timestamp field for new news articles.
    The timestamp is updated if the news article is updated.

    I have two news pages, current and archive.

    I want to select the top 10 news articles based on date for the current news page and select all other news articles for the archive page.

    If i use select top 10 * i get the top 10 based on newsID (primary key)
    I need it based on the date field.

    Thanks in advance
    Andy

  2. #2
    Join Date
    Feb 2008
    Posts
    120
    Please ignore my previous post. It does actually do what i want
    I thought it was basing it on the newsID, but it is actually basing it on the date. oops feel silly now.

    I do have another question however. How do i select everything other than the top 10? this will be for the archive page.

    Thanks again
    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select the top 10 in a subquery which you can use to exclude records from an outer query.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2008
    Posts
    120
    Hi blindman
    Thanks, but how to id do that?
    Do you have a sample at all.

    This is what i have to select the first ten
    SELECT TOP 10 * FROM Content ORDER BY TimeStamp DESC

    The news articles will continue to grow so i won't know the total number of records.

    Thanks again
    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SELECT *
    FROM Content
    WHERE [PrimaryKey] not in (SELECT TOP 10 [PrimaryKey] FROM Content ORDER BY TimeStamp DESC)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2008
    Posts
    120
    Hi Blindman
    Thanks for that.
    I've tried it but the dates are not in the correct order.
    Im using the code below
    Please see screengrab

    SELECT *
    FROM Content
    WHERE [ContentID] not in (SELECT TOP 10 [ContentID] FROM Content
    ORDER BY TimeStamp DESC)

    Andy
    Attached Thumbnails Attached Thumbnails orderby.jpg  

  7. #7
    Join Date
    Feb 2008
    Posts
    120
    Sorry Blindman i've fixed it.
    Just moved the ORDER BY TimeStamp DESC outside of the end bracket.

    Thanks for your help, works a treat now
    Cheers

    Andy

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You must also define an ORDER BY on the result set.
    Code:
    SELECT *
    FROM Content
    WHERE [ContentID] not in 
              (SELECT TOP 10 [ContentID] 
              FROM Content
              ORDER BY TimeStamp DESC
              )
    ORDER BY TimeStamp DESC
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by AndyJay View Post
    ...Just moved the ORDER BY TimeStamp DESC outside of the end bracket...
    That is not a correct solution.
    For the "TOP 10" to work in a predictable way it needs an ORDER BY.
    If you want the end result to display as you wanted, you need another ORDER BY.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Feb 2008
    Posts
    120
    Thanks Wim

    Makes sense, i've changed my code.

    Much appreciaed guys

    Andy

Posting Permissions

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