Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to select data for a unique column ?

Posted on 2006-11-06
10
Medium Priority
?
736 Views
Last Modified: 2011-09-20
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.
0
Comment
Question by:pankajtiwary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Expert Comment

by:ivanovn
ID: 17883153
SELECT name, manager
FROM table1
WHERE name IN (SELECT DISTINCT name FROM table1)
0
 
LVL 10

Expert Comment

by:ivanovn
ID: 17883160
Wait, that's wrong.
0
 
LVL 4

Author Comment

by:pankajtiwary
ID: 17883225
Just for information, I am using Informix.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17883354
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.  
0
 
LVL 4

Author Comment

by:pankajtiwary
ID: 17883539
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.
0
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 1000 total points
ID: 17883557
All tables, by definition, have a primary key.  If you have not explicitly defined one, then the key is every column in the table.  That may or may not be desirable in this case (from the sample of data you provided it sounds reasonable).  You may want to make sure it is explicitly declared as such so the DBMS will at least know what you're doing.

In any case, SELECT MIN(manager) FROM the_table GROUP BY name
... is just as good as anything.  Pick your favorite aggregation function and run with it, basically, since you don't care which row gets returned.
0
 
LVL 4

Author Comment

by:pankajtiwary
ID: 17883657
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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17884143
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
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17884340
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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17884353
I guess i was too late already :)
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question