Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Simple "SELECT DISTINCT" quickie. Probably.

    I've got a forum running off two basic tables: forum_threads and forum_posts:

    forum_threads columns:
    id
    subject

    forum_posts columns:
    id
    thread_id
    post_date
    body

    I've got the following script, that creates a list of forum threads ordered by which threads were most recently added to (ie: which enrty in the forum_posts table has the most recent date). If that makes sense:

    SELECT TOP 10 CONVERT(varchar(20 ), forum_posts.post_date) AS last_post_date, forum_threads.id, forum_threads.subject
    FROM forum_threads INNER JOIN forum_posts ON forum_posts.thread_id = forum_threads.id
    ORDER BY forum_posts.post_date DESC

    Trouble is, if there's more than one entry on the forum_posts table with a particular thread_id, then it pulls out all of 'em. What I want it to do is pull out the top 10 recently-added-to threads but only those with distinct thread_id's.

    I tried sticking DISTINCT in front of forum_threads.id, but that threw an error.

    Can anyone point me in the right direction? Many thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
      SELECT TOP 10 
             CONVERT(varchar(20)
                   , MAX(forum_posts.post_date) )
                        AS last_post_date
           , forum_threads.id
           , forum_threads.subject 
        FROM forum_threads INNER JOIN forum_posts 
          ON forum_posts.thread_id = forum_threads.id 
    GROUP BY forum_threads.id
           , forum_threads.subject 
    ORDER BY last_post_date DESC
    caution: untested

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Aha! That's how you do it! Thanks very much, I'd have never figured that one out myself.

Posting Permissions

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