need help with an SQL query.. you'd think DISTINCT() would do this but noooo

[Israfel]

I've written a forum which has a topics table and a posts table and I'm trying to get five recent topics and the last post in each topic in one query.

You can see what I have at http://www.comeplaydying.com/

For the most part it's working, the problem I am trying to solve is that I only want each topic to show up once, but what's happening is mysql is returning the last five posts and matching them with their topics when i want the last five topics to be matched with posts.

Here is the query I'm using for the page as it is now.

SELECT topics.*, posts.*, forums.forumid, forums.name, users.username
FROM topics, forums
LEFT JOIN users ON users.userid=topics.userid
LEFT JOIN posts ON topics.topicid=posts.parentid
WHERE topics.topicid>0 AND forums.forumid=topics.forumid
ORDER BY topics.timestamp DESC, posts.posted DESC
LIMIT 0,5

You would think, since I'm SELECTing from topics and JOINing on posts it would get topics and match posts to them but no such luck. Does anyone have any ideas for this?

[Ramiro S]

try using GROUP BY with the column you dont want to be repeated.

[Israfel]

Thank you very much but now I have a new problem...


The GROUP BY not only shows topics once but the post it shows for each topic is the first post in the topic. Is there any way I can change this so it only shows the last post in the topic? I have ORDER BY posts.postid DESC but that isn't helping.

[r937]

five recent topics and the last post in each topic

select topics.foo
     , topics.bar
     , topics.timestamp 
     , p1.baz
     , p1.qux
     , p1.posted
     , forums.forumid
     , forums.name
     , users.username 
  from topics
inner
  join forums 
    on topics.forumid 
     = forums.forumid
inner
  join users 
    on topics.userid 
     = users.userid
inner
  join posts p1
    on topics.topicid
     = p1.parentid 
inner
  join posts p2
    on topics.topicid
     = p2.parentid 
 where topics.topicid > 0 
group
    by topics.foo
     , topics.bar
     , topics.timestamp 
     , p1.baz
     , p1.qux
     , p1.posted
     , forums.forumid
     , forums.name
     , users.username  
having p1.posted 
     = max(p2.posted)     
order 
    by topics.timestamp desc
limit 0,5