Page 1 of 1

Selecting more than 1 column using Distinct?

Posted: Wed Nov 05, 2003 8:23 am
by dschad
Hi,

I have the following table called actions with the following fields:

action_id tinyint unique auto_increment, (this is the primary key)
action_name varchar(20),
service_id tinyint

I am trying to use the following sql query to return all distinct action names and their corresponding action_id:

select distinct action_name,action_id from actions

However, this returns all of the records in the table. I assume because it is looking for distinct action_id as well? All of the action_id's will be distinct because action_id is the primary key.

I only want it to search for distinct action_name and not the distinct action_id like the query is doing at the moment, but I do want it return the action_id value along with the distinct action_names.

I hope this makes sense!

Thanks in advance,
Dan

Re: Selecting more than 1 column using Distinct?

Posted: Wed Nov 05, 2003 8:28 am
by Weirdan
dschad wrote: I only want it to search for distinct action_name and not the distinct action_id like the query is doing at the moment, but I do want it return the action_id value along with the distinct action_names.

I hope this makes sense!
Suppose you have two rows with different action_ids but with same action_names... which action_id you're expecting to see?

Re: Selecting more than 1 column using Distinct?

Posted: Wed Nov 05, 2003 9:35 am
by dschad
Weirdan wrote:Suppose you have two rows with different action_ids but with same action_names... which action_id you're expecting to see?
I would expect the query to return the first action name and its action_id that it finds and ignore and other records with the same action_name.

I'm not 100% sure though, because the query is not working as I thought it would?

Posted: Wed Nov 05, 2003 9:52 am
by JAM
You can play around with GROUP BY.

Code: Select all

"select info, max(value) from test group by info"
Other idea:

Code: Select all

"select info, value from test order by rand() limit 0,1"
Browse the mysql manual for rand(), order by, limit and group by to get abit more info about how to use them. Hope that helped.

Posted: Wed Nov 05, 2003 10:24 am
by dschad
Thanks for the help guys, I've now got the query working. As you suggested JAM, I used the Group By option instead of using distinct and i'm now getting the results I was expecting.

Thanks once again.