DISTINCT and select all :? confused!!

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
BETA
Forum Commoner
Posts: 47
Joined: Fri Jul 25, 2008 3:21 am

DISTINCT and select all :? confused!!

Post 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!
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: DISTINCT and select all :? confused!!

Post by novice4eva »

In your particular table of interest, list out the column names that you want and then do the select distinct col1,col2......
BETA
Forum Commoner
Posts: 47
Joined: Fri Jul 25, 2008 3:21 am

Re: DISTINCT and select all :? confused!!

Post 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!
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: DISTINCT and select all :? confused!!

Post 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.
BETA
Forum Commoner
Posts: 47
Joined: Fri Jul 25, 2008 3:21 am

Re: DISTINCT and select all :? confused!!

Post 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!
BETA
Forum Commoner
Posts: 47
Joined: Fri Jul 25, 2008 3:21 am

Re: DISTINCT and select all :? confused!!

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DISTINCT and select all :? confused!!

Post 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......
There are 10 types of people in this world, those who understand binary and those who don't
BETA
Forum Commoner
Posts: 47
Joined: Fri Jul 25, 2008 3:21 am

Re: DISTINCT and select all :? confused!!

Post 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 :)
Post Reply