homepage Welcome to WebmasterWorld Guest from 54.205.207.53
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Trouble with DISTINCT
IntegrityWebDev



 
Msg#: 4170105 posted 5:15 pm on Jul 14, 2010 (gmt 0)

I thought I understood DISTINCT but I'm doing something wrong (using MySQL).

I have a table with several columns, and I need to return results based on DISTINCT col2. But I need more than just col2 returned...so I tried:

SELECT DISTINCT col2, col1 FROM table WHERE col3 <> '' ORDER BY col3

but this has returned the equivalent of

SELECT col2, col1 FROM table WHERE col3 <> '' ORDER BY col3


Any Thoughts?

Thanks!
Chris

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4170105 posted 5:35 pm on Jul 14, 2010 (gmt 0)

SELECT DISTINCT col2, col1 will give you the distinct combinations of col2 and col1, not just col2.

Let's say you have the following:
col1 | col2
apples | red
apples | green
grapes | green
grapes | red

If you just want DISTINCT col2, you will get:
red
green

Think about it logically. If you want to show both col2 and col1, there are 2 possible values for each col2- apples and grapes. So it's showing you what you asked for.

[edited by: LifeinAsia at 5:37 pm (utc) on Jul 14, 2010]

IntegrityWebDev



 
Msg#: 4170105 posted 5:35 pm on Jul 14, 2010 (gmt 0)

Nevermind...for the 2nd time today I search forever, then post here, then find the answer moments later.

Needed to use GROUP BY:

SELECT DISTINCT col2, col1 FROM table WHERE col3 <> '' ORDER BY col3 GROUP BY col2

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved