Selecting more than 1 column using Distinct?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dschad
Forum Newbie
Posts: 7
Joined: Wed Nov 05, 2003 8:23 am

Selecting more than 1 column using Distinct?

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Selecting more than 1 column using Distinct?

Post 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?
dschad
Forum Newbie
Posts: 7
Joined: Wed Nov 05, 2003 8:23 am

Re: Selecting more than 1 column using Distinct?

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
dschad
Forum Newbie
Posts: 7
Joined: Wed Nov 05, 2003 8:23 am

Post 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.
Post Reply