Page 1 of 1
DISTINCT and select all :? confused!!
Posted: Tue Dec 09, 2008 5:07 pm
by BETA
Hello again!
Long time since my last visit

well I have a new news system and a mysql table what i want to do is take the rows in the topic but not the answers to this topic so...
a
could be made if i'm not wrong but then it would only select the topic_id row...
What i want is that it takes care of not repeating a topic (to make sure it doesn't take the replies which have the same topic id) but select all the rows in that topic... something like
but that doesn't work.
So im confused and don't know what to do.
Any help is appreaciated!
Thx in advance guys!
Re: DISTINCT and select all :? confused!!
Posted: Tue Dec 09, 2008 7:53 pm
by novice4eva
In your particular table of interest, list out the column names that you want and then do the select distinct col1,col2......
Re: DISTINCT and select all :? confused!!
Posted: Wed Dec 10, 2008 7:26 am
by BETA
Can you explain that a little bit more or show me an example please?
Thx!
EDIT:
Omg i was so stupid
Code: Select all
SELECT * ... GROUP BY topic_id DESC in stead of ORDER BY topic_id DESC
did the trick in this case

thx anyway!
Re: DISTINCT and select all :? confused!!
Posted: Thu Dec 11, 2008 8:54 pm
by novice4eva
Aie... ok i was thinking that you needed to fetch more than one column: along with topic_id some other fields too...anyways since you needed just topic_id, i think the first query select distinct topic_id would have worked perfectly....About select * .. group by topic_id, if is working then it should be returning the same result set as did the select distinct one.
Re: DISTINCT and select all :? confused!!
Posted: Fri Dec 12, 2008 6:59 am
by BETA
mmm it doesn't because with select * .. group by topic_id u select all and group them by topic_id but with distinct it only retreives the topic_id column not the rest of the info...
Well don't care, anyway i appreaciate you read the post and took the time to think in a solution.
Thx!
BB!
Re: DISTINCT and select all :? confused!!
Posted: Fri Dec 12, 2008 3:05 pm
by BETA

not working now... i tried it a bit further and it doesn't work as expected
it continues showing comments...
what was your idea?
thx in advance!
Re: DISTINCT and select all :? confused!!
Posted: Sun Dec 14, 2008 7:25 am
by VladSun
novice4eva wrote:In your particular table of interest, list out the column names that you want and then do the select distinct col1,col2......
Re: DISTINCT and select all :? confused!!
Posted: Sun Dec 14, 2008 8:24 am
by BETA
well don't care i have solved this as follows:
Code: Select all
SELECT phpbb_posts.* FROM ( SELECT topic_id, MIN(post_id) AS min_post
FROM phpbb_posts
WHERE forum_id IN ($forum_id) GROUP BY topic_id ) AS m
INNER JOIN phpbb_posts
ON phpbb_posts.topic_id = m.topic_id
AND phpbb_posts.post_id = m.min_post
ORDER
BY phpbb_posts.topic_id DESC
LIMIT $offset, $newsperpage"
thx anyway
