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 :lol:
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

Code: Select all

SELECT DISTINCT topic_id
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

Code: Select all

SELECT * DISTINCT topic_id
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 :banghead:

Code: Select all

SELECT * ... GROUP BY topic_id DESC in stead of ORDER BY topic_id DESC
did the trick in this case :lol:
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 :banghead:
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 :)