SQL - How do you select the first row (or any unique row) of multiple rows with the same ID?

Question

Let's say I have a query result that looks as follows:

ID    NAME    Phone
----  ----    -----
1     Bob     111-111-1111
1     Bob     222-222-2222
1     Bob     333-333-3333
2     Stan    555-555-5555
3     Mike    888-888-8888
3     Mike    777-777-7777

I want to return just a single row instance of each ID value. It doesn't matter to me which one of the multiple rows I get - the first in the set is ok.

So a possible result would be:

ID    NAME    Phone
----  ----    -----
1     Bob     111-111-1111
2     Stan    555-555-5555
3     Mike    888-888-8888

Solution 1

For SQL Server [edit] and MS Access, you can do

SELECT [ID], [Name], MIN(Phone) as PhoneNumber
FROM PhoneNumbers
GROUP BY [ID], [Name]

This will return

ID    NAME    Phone
----  ----    -----
1     Bob     111-111-1111
2     Stan    555-555-5555
3     Mike    777-777-7777

You might want to add some sort of unique key to the table, just a thought.

Solution 2

We can probably also help you in a direction, if you provide us with the T-SQL statement that gave you the first results you got, with some information how that can be re-written to get the results you wanted.

Comments

[Howiecamp]

@Tom - right now Access 2010 (yes I'm embarrassed to admit it) but really my question is meant to be as general as possible.

[Tom H]

Heh... no reason to be embarrassed. Sometimes Access is the right tool for the job. :)

[Howiecamp]

Thanks. This isn't a table but rather a result from a previous query. I am going to quickly try this out...

[Howiecamp]

I guess I was thinking it shouldn't matter since I could arrive at this result set in multiple different ways. I really only need to solve from this point forward.

[David-W-Fenton]

Alternatives to Min() are Max(), First() and Last(). The latter two will return different data based on the sort order.

[tcash21]

How can you accomplish this with many columns though that would need to be aggregated with a MIN() statement? I may end up just writing it all out.