CODEWITHSUNDEEP
sqlms-accessgreatest-n-per-group
Howiecamp
Howiecamp

Reputation: 3111

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

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

Upvotes: 1

Views: 12519

Answers (2)

Arild R
Arild R

Reputation: 144

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.

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

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.

Upvotes: 7

Related Questions