CODEWITHSUNDEEP
sqlsql-server-2005distinct
H4p7ic
H4p7ic

Reputation: 1923

Selecting all rows in which id is distinct

Hi i need some advice on how to do a select statement on selecting all rows in which the phone number acts as a measure of "distinction".

Example of what i have.

|ID |Name |Phone Number| Address  |
|   |     |            |          |
|1  |John | 1234567    | A.Road 1 |
|1  |John | 1234567    | B.Road 2 |
|2  |Jane | 7654321    | C.Road 3 |
|3  |Jim  | 7654321    | C.road 3 |

Example of what i want:

|ID |Name |Phone Number| Address  |
|   |     |            |          |
|1  |John | 1234567    | A.Road 1 |
|2  |Jane | 7654321    | C.Road 3 |

Regarding on which of the rows SQL chooses to pic on the result doesn't matter only that the whole row is available and that it makes a selection of distinct phone numbers. Hope you understand what i'm trying to do here.

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

ANSI SQL supports the row_number() function, which is a typical solution:

select t.*
from (select t.*,
             row_number() over (partition by phone_number order by id) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 4

Related Questions