Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
12-11-2009, 01:27 AM #1
- 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
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
do i have to use multiple queris?
12-11-2009, 01:58 AM #2
Ahhh, you are going to kick yourself over this one.
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.Code:SELECT forumid, MAX(createdate) AS lastCreatedSubforum, COUNT(*) AS numberOfSubforums FROM subforum GROUP BY forumid ORDER BY lastCreatedSubforum DESC
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: