Trouble with DISTINCT

Forum Moderators: open

Message Too Old, No Replies

Trouble with DISTINCT

IntegrityWebDev

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

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

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