Hello and welcome to our community! Is this your first visit?
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
    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.

    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
    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?


  2. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    Ahhh, you are going to kick yourself over this one.
    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.
    Be yourself. No one else is as qualified.

  3. 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