Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jun 2009
    Posts
    31
    Thanks
    7
    Thanked 1 Time in 1 Post

    order by and gorup by in single sql statement

    Hi i have a table subforum with structure as follows

    subforumid - int auto increment
    forumid -int
    userid -int
    projectid -int
    subject - longtext
    createdate - datetime
    deleted - int

    now i want to display the number of replies and the last post's time.

    Code:
    select createdate, forumid, count(subforumid) from subforum  group by forumid order by createdate desc

    it runs but does not give expected result. it order the createdate in result created by
    Code:
    select createdate, forumid, count(subforumid) from subforum  group by forumid
    i know i am doing something wrong here but cannot figure out.
    do i have to use multiple queris?

    thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,596
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Ahhh, you are going to kick yourself over this one.
    Code:
    SELECT forumid, MAX(createdate) AS lastCreatedSubforum, COUNT(*) AS numberOfSubforums
    FROM subforum
    GROUP BY forumid
    ORDER BY lastCreatedSubforum DESC
    You were done in by a "feature" of MySQL. Most DBs would have given you an error from that query, because you did GROUP BY *only* of forumid and did not put createdate into either an aggregate function or into the group by. MySQL is lenient, but then doesn't give you the answer you expect.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    skywaves (12-11-2009)


  •  

    Tags for this Thread

    Posting Permissions

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