asked on

How to select data for a unique column ?

Hi all,

I have a table with the following data.

name manager
------- -----------
Pankaj Suneel
Gaurav Suneel
Pankaj Ahmed
Sangeetha Ahmed
Gaurav Ahmed

For each unique name, I want to get any one row from the table. I mean to say, since there are 3 unique names (Pankaj, Gaurav, Sangeetha), I want any rows for them. The sample output would be

Pankaj Suneel (Or Pankaj Ahmed will also do)
Gaurav Suneel (Or Gaurav Ahmed will also do)
Sangeetha Ahmed (Because there is a single row).

I am confused. Please help me.

Thanks.

ivanovn

SELECT name, manager
FROM table1
WHERE name IN (SELECT DISTINCT name FROM table1)

ivanovn

Wait, that's wrong.

pankajtiwary

ASKER

Just for information, I am using Informix.

VoteyDisciple

You could probably do...

SELECT name, manager
FROM the_table
WHERE primary_key IN (SELECT MIN(primary_key) FROM the_table GROUP BY name)

Here we just pick which row gets shown by which has the lowest primary key.

pankajtiwary

ASKER

These are the only columns present in the table. And actually both are referencing two other tables. So, no primary key is available as far as I understand.

ASKER CERTIFIED SOLUTION

VoteyDisciple

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pankajtiwary

ASKER

SELECT MIN(manager) FROM the_table GROUP BY name;

It worked :-) Actually the data I have are numbers and not varchars (name and manager are just example columns). Actually it was a simple problem, but some times simple problems take more time. Thanks everyone for help expecially VoteyDisciple.

Jinesh Kamdar

VoteyDisciple, can u explain if ur query does exactly wat is required ? Please correct me if im wrong, but this is wat i believe vil happen : -

>> SELECT MIN(manager) FROM the_table GROUP BY name --- selects a single manager for every name.
>> SELECT name, manager FROM the_table WHERE manager IN (SELECT MIN(manager) FROM the_table GROUP BY name) --- selects name, manager from the_table having manager in the the above list i.e. select name, manager from the_table where manager is in the list of a manager for every name, NOT that specific one. This query night return multiple records for the same name, which is not desired. I suppose u missed and a WHERE condition.

I guess the below query wud the requried : -

SELECT name, (SELECT MIN(manager) FROM the_table WHERE name = t.name)
FROM the_table t
GROUP BY name

VoteyDisciple

Yeah, I didn't properly think that through after revising what the primary key is. You're right; my query will just select... well, nothing of particular value -- all the names associated with a bunch of managers selected by no thought-out criterion.

jinesh_kamdar's solution is more appropriate in this schema -- select the name, and the minimum manager for that name.

Jinesh Kamdar

I guess i was too late already :)