Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2001
    Posts
    8

    Unanswered: SELECT DISTINCT(a) FROM table ORDER BY b;

    I am in the process of creating a user forum that is very similar to this one. I'm using two tables for this particular application.

    TOPICS
    topicID {primary key}
    TopicName

    POSTS
    postID {primary key}
    topicID {foreign key}
    PostDate
    PostUser
    PostContents

    I need to list the distinct topics by PostDate in descending order. The ideal recordset would look like the following:

    topicID, TopicName, PostUser, PostDate

    Can you gather all of this information in one SQL statement?

    I want to write:
    SELECT DISTINCT(topicID) FROM posts ORDER BY PostDate;

    However, it won't let me sort by a field not contained in the DISTINCT statement.

    While you're at it, can you give me the number of replies for each distinct topic in the recordset?

    Muchas gracias.

  2. #2
    Join Date
    Dec 2003
    Posts
    17
    SELECT TopicID, TopicName, PostUser, PostDate
    FROM TOPICS t
    INNER JOIN POSTS p ON t.TopicID = p.TopicID

    SELECT TopicID, Count(*) AS Replies
    FROM POSTS
    ORDER BY PostDate

    Hope that helps.

  3. #3
    Join Date
    Nov 2001
    Posts
    8
    Originally posted by sjp
    SELECT TopicID, Count(*) AS Replies
    FROM POSTS
    ORDER BY PostDate
    When I try ...

    SELECT TopicID, Count(*) AS Replies
    FROM POSTS
    ORDER BY PostDate

    ... I get the following error message:

    Column 'post.topicID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

    When I GROUP BY topicID, PostDate is invalid.

    Any thoughts?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SELECT TopicID, Count(*) AS Replies
    FROM POSTS
    GROUP BY TopicID
    ORDER BY Max(PostDate)

  5. #5
    Join Date
    Dec 2003
    Posts
    17
    Sorry about that give this a try. It will give you the number of posts for each topic and will order by the first post date. If you want to order by the last post date, change MIN to MAX.

    SELECT TopicID, MIN(PostDate), Count(*) AS Replies
    FROM POSTS
    GROUP BY TopicID
    ORDER BY PostDate

  6. #6
    Join Date
    Nov 2003
    Posts
    48
    This is the query you want.

    Code:
    select a.topicID, a.TopicName, b.PostUser, b.PostDate
    from topics a, posts b, 
         (select topicID, max(PostDate) as max_PostDate
          from posts
          group by topicID
         ) c
    where a.topicID = b.topicID
      and b.topicID = c.topicID
      and b.PostDate = c.max_PostDate
    Shianmiin

  7. #7
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20
    Originally posted by gitzo
    When I try ...

    SELECT TopicID, Count(*) AS Replies
    FROM POSTS
    ORDER BY PostDate

    ... I get the following error message:

    Column 'post.topicID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

    When I GROUP BY topicID, PostDate is invalid.

    Any thoughts?
    SELECT TopicID, Count(*) AS Replies
    FROM POSTS
    GROUP by TopicID, PostDate
    ORDER BY PostDate


    But hey, how can u know if the post is a reply or not? I think there is missing field in your design.

    MY query above is correct but I think it cant give you the desired result.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mine's a variation of shianmiin's, except mine allows for topics that may not have any posts
    Code:
    select T.topicID, TopicName
         , PostUser, PostDate
      from topics T
    left outer
      join posts P
        on T.topic.id = P.topic.id
     where PostDate = 
           ( select max(Postdate)
               from posts
              where topicID = T.topic_ID )
    rudy
    http://r937.com/

  9. #9
    Join Date
    Nov 2003
    Posts
    48
    Your query won't return topics without any posts because the where clause will prevent records with null PostDate from being returned.

    Originally posted by r937
    mine's a variation of shianmiin's, except mine allows for topics that may not have any posts
    Code:
    select T.topicID, TopicName
         , PostUser, PostDate
      from topics T
    left outer
      join posts P
        on T.topic.id = P.topic.id
     where PostDate = 
           ( select max(Postdate)
               from posts
              where topicID = T.topic_ID )
    rudy
    http://r937.com/
    Shianmiin

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you're right

    less haste, more speed

    change WHERE to AND


Posting Permissions

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