Group by not working very well

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

Group by not working very well

Post by billman »

I'm having some problems getting the following mySQL query to work right. I'm trying to sort the newest forum posts from Ikonboard using mySQL. The posts are in ib_forum_posts, the topics are in ib_forum_topics, and I want to list it descending by the newest posts, and group together the topics so they only display once.

Code: Select all

select * from ib_forum_posts, ib_forum_topics where ib_forum_posts.TOPIC_ID = ib_forum_topics.TOPIC_ID order by ib_forum_posts.POST_ID desc limit 0,10
It works almost perfectly, but of course it can display the same topics more the once, since nothing is grouped. So I add a group by in there, and it messes up the whole order. Group by seems to be getting the LAST post instead of the newest one, it's like the other ones don't exist when I'm using group by.

Code: Select all

select * from ib_forum_posts, ib_forum_topics where ib_forum_posts.TOPIC_ID = ib_forum_topics.TOPIC_ID group by ib_forum_posts.TOPIC_ID order by ib_forum_posts.POST_ID desc limit 0,10
Can anyone help? I can't seem to find a prewritten mySQL query to display the newest posts from Ikonboard, which really sucks. I hate writing complex queries, I always fail :(
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

Post by billman »

Anyone? :cry:
Post Reply