rendered paste body//categories table
id name category
------ -------------------- --------
9 Town Square 1
8 About Anything 1
7 New 1
10 Suggestions and View 1
11 Buying 2
12 Selling 2
// list
id title content forumid catid postedby date_created
------ ------- ------------------------------- ------- ------ -------- -------------------
1 test9_1 Lucky is the name of Don Nelson 9 1 john 1992-05-24 10:00:00
2 demo2 test 8 1 john 1997-05-09 12:00:02
3 demo7 testasjdfjasdfjasdajhsdgdsasad 7 1 test 2003-05-20 09:00:00
4 demo10 sadasdasdasdadsads 10 1 test 2007-01-01 12:00:00
5 demo11 testasjdfjasdfjasdajhsdgdsasad 11 2 test 2007-06-24 12:00:00
6 demo12 sadasdasdasdadsads 12 2 test 2000-12-25 01:00:00
32 test9_2 asfjskadfhsdafsad 9 1 demo 2007-05-24 12:00:00
33 test9_3 check if the latest query is ok 9 1 john 2007-05-24 15:09:11
// sql - works but wrong
select id, title, forumid, title, count(*)
from forum_list fl1
where date_created = (select max(date_created) from forum_list fl2 where fl1.id = fl2.id)
group by forumid;
// sql result
id title forumid title count(*)
------ ------- ------- ------- --------
3 demo7 7 demo7 1
2 demo2 8 demo2 1
1 test9_1 9 test9_1 3
4 demo10 10 demo10 1
5 demo11 11 demo11 1
6 demo12 12 demo12 1
// sql result SHOULD be
id title forumid title count(*)
------ ------- ------- ------- --------
3 demo7 7 demo7 1
2 demo2 8 demo2 1
1 test9_3 9 test9_1 3
4 demo10 10 demo10 1
5 demo11 11 demo11 1
6 demo12 12 demo12 1
Try this
--------------
select id, title, forumid, title, count(*)
from (select max(date_created) mdate_created from forum_list) fl2, forum_list fl1
where date_created = fl2.mdate_created and fl1.id = fl2.id
group by fl1.forumid;
---Sadi