Problems with Distinct

[lumstar]

I have the following:

ID    Name
-----------
1     Bob
2     Jimmy
3     Cheryl
1     Susan
3     Michael

I want to select only one Name for each of the distinct ID Values. The following is what I want:

ID    Name
-----------
1     Bob
2     Jimmy
3     Cheryl

Or something similar.

I have tried "SELECT DISTINCT ID, Name from Table" but it is not working for me, I don't think Group By applies because I am not using aggregate functions. What should I do?

Thanks,
Lumstar

 

[MDXer]

How do you Know that the return for ID 3 should be cheryl and not Michael? are you after just the first name that is associated with an ID?

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[lumstar]

I don't care which tuple gets selected, so long as there is only one for each ID. Thanks.

Lumstar
______________________________________
And The Star Continues To Shine....

 

[amrita418]

I do not see a straight forward method to get this but here are a couple of options you can pursue:

1) Create an Identity column on the table and use min(Identity Column) to get a single tuple per ID.

2) Use something like min(len(name)) to get only one value per ID. The problem with using this approach is that you might get more than one entry per ID if the length of both the names for a given ID are same.

Regards,
AA

 

[SQLSister]

Group by is exactly waht you want.

Select min(Name) from Table Group By ID

This will get you the first name alphabetically for each id.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[lumstar]

I also want to select the ID though. I tried:

SELECT ID, Name FROM Table GROUP BY ID

It returns a sql error.

How would I do this?

TIA

Lumstar
______________________________________
And The Star Continues To Shine....

 

[SQLSister]

That's why you have to use an aggregate function on name. Since you don;t care which name it returns just use min or max.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[amrita418]

Good point sqlsister! I overlooked the fact that lumstar's requirement id not have a preference of one row over other.

Regards,
AA