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
Selecting more than 1 column using Distinct?
Moderator: General Moderators
Re: Selecting more than 1 column using Distinct?
Suppose you have two rows with different action_ids but with same action_names... which action_id you're expecting to see?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!
Re: Selecting more than 1 column using Distinct?
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.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'm not 100% sure though, because the query is not working as I thought it would?
You can play around with GROUP BY.
Other idea:
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.
Code: Select all
"select info, max(value) from test group by info"Code: Select all
"select info, value from test order by rand() limit 0,1"