PHP MYSQL multiple or single DISTINCT/UNIQUEvalues

Question

Currently I have this query:

SELECT column1,column2 FROM table

column1 needs to be distinct, column2 does not.

SELECT DISTINCT column1, NON-DISTINCT column2 FROM table

Now I know that doesn't make sense but I need column1 to be distinct and column2 to be anything. How would I do that.

Do you want a single bla1 at random whenever you have two or more PIDs? — Vinko Vrsalovic
– Vinko Vrsalovic, Commented Sep 11, 2009 at 11:37
well i don't want to do "select * from table" because i only want to get back "pid" and "bla1" - where 'pid' is distinct and 'bla1' isn't. the outcome being rows 1-3 — Mickey
– Mickey, Commented Sep 11, 2009 at 11:54

dnagirl · Accepted Answer · 2009-09-11 12:11:54Z

2

select pid, group_concat(distinct bla1) as bla1s
from table
group by pid;

The above will get you 1 row for each pid and you'll be able to see if there are extra bla1s without introducing a new column or having to settle for a random choice of multiple bla1s.

answered Sep 11, 2009 at 12:11

dnagirl

20.5k13 gold badges86 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

martin.malek · Accepted Answer · 2009-09-11 13:56:42Z

1

Try this (fastest):

SELECT *
FROM `table`
GROUP BY pid
HAVING min( id )

second (slower) option:

select *
from `table` t1
where
    t1.id = (select min(id) from `table` t2 where t1.pid = t2.pid)

edited Sep 11, 2009 at 13:56

answered Sep 11, 2009 at 11:36

martin.malek

2,2482 gold badges21 silver badges32 bronze badges

7 Comments

Mickey Over a year ago

:( isn't there a way to do it without the "group/min"?

DisgruntledGoat Over a year ago

Why don't you want the grouping? SELECT DISTINCT is for getting unique values only, other values you try and select will be ambiguous. Should it select 2 or 7 as the bla1?

martin.malek Over a year ago

There is no way how to do that without group, or there is - subselect, but this will be slow. Or you can delete these records if you don't want them. Why you don't want to use group by?

Mickey Over a year ago

goat -> i want to do this (select pid,bla1 from table) but i only want pid to be distinct // malek -> because i think there is an easier way to do it.

martin.malek Over a year ago

without group by, but not easier :-) I think you will not find easier way: select * from test t1 where t1.id = (select min(id) from test t2 where t1.pid = t2.pid)

|

Collectives™ on Stack Overflow

PHP MYSQL multiple or single DISTINCT/UNIQUEvalues

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related