Welcome to WebmasterWorld Guest from 54.145.136.73

Forum Moderators: open

Trouble with DISTINCT

   
5:15 pm on Jul 14, 2010 (gmt 0)

5+ Year Member



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
5:35 pm on Jul 14, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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]

5:35 pm on Jul 14, 2010 (gmt 0)

5+ Year Member



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
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month