How to distinct duplicate rows(by field) in inner join clause

Question

I have two tables:

Customers -> CustomerId, Name 

Phones -> PhoneId , CustomerId , Phone

This tables are join through CustomerId field. Every customer have more than one phone in phones table. I need to get only one of them. How can i do this.

SELECT 
       Customers.CustomerId , 
       Customers.Name ,
       Phones.Phone
FROM Customers 
     INNER JOIN Phones ON Customers.CustomerId = Phones.CustomerId

In this case i get something like:

+----------+----+-----------+
|CustomerId|Name|Phone      |
+----------+----+-----------+
|1010      |Jack|999-999-999|
+----------+----+-----------+
|1010      |Jack|888-888-888|
+----------+----+-----------+
|1010      |Jack|111-111-111|
+----------+----+-----------+
|2020      |Pit |123-123-123|
+----------+----+-----------+
|2020      |Pit |321-321-321|
+----------+----+-----------+

But i need only one customer and one phone number like:

+----------+----+-----------+
|CustomerId|Name|Phone      |
+----------+----+-----------+
|1010      |Jack|888-888-888|
+----------+----+-----------+
|2020      |Pit |123-123-123|
+----------+----+-----------+

I tried to do it using subquery:

   SELECT 
   c.CustomerId , 
   c.Name ,
   SELECT ( p.Phone FROM Phones WHERE p.CustomeId=c.CustomerId FETCH FIRST 1 ROWS ONLY)
   FROM Customers c

But it works VERYYYY SLLOOOW thats why i cant use subquery. How can i do it anyway else?

We hope you have some additional details for phone numbers (country code is nice). Note that you shouldn't be storing numbers formatted (that's a display-level function, and is context-sensitive anyways), and would probably want a separate column for "mnemonic form entered" (for stuff like `CUT-TREE`) - I'm not certain all countries use the same encoding, if at all. — Clockwork-Muse, Jan 14 '14 at 09:45
So when you have multiple phone numbers, do you have some way to decide which one is the best to pick? An arbitrary choice may not be a good solution. — WarrenT, Jan 14 '14 at 22:55

WarrenT · Answer 1 · 2014-01-14T02:43:01.153

The Row_Number() function will help you out.

WITH X AS
(SELECT C.CustomerId
      , C.Name
      , P.Phone
      , ROW_NUMBER() OVER (PARTITION BY P.CustomerID) AS Pick
  FROM Customers C
  JOIN Phones    P   ON C.CustomerId = P.CustomerId 
)
SELECT CustomerID
     , Name
     , Phone
  FROM X
  WHERE Pick = 1

This will assign a cardinal number to each phone number pertaining to a given customer, and choose only the first one. Now in this case, we are arbitrarily picking which phone number is the first one.

Suppose you had some other field in your Phones table that might help you decide which is the best to choose. Let's say you have a Last_Used timestamp, and the best number to pick can be the one used most recently. Then you can use an expression like this:

ROW_NUMBER() OVER (PARTITION BY P.CustomerID  ORDER BY P.Last_Used desc)

Of course, if you were talking about some other type of field, it might possibly make sense to simply use MIN() or MAX(), and GROUP BY CustomerID. But I can't see any rationale to do so with phone numbers.

score 0 · Answer 2 · answered Jan 13 '14 at 10:28

0

best option is to use pivot . for more information visit http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ here. also by using this you are able to view all of your telephone numbers too....

answered Jan 13 '14 at 10:28

Biby Augustine

425
1
3
16

Is it possible in DB2 ? – Graffiti908 Jan 13 '14 at 10:34
just checkout here http://stackoverflow.com/questions/13579143/how-can-i-pivot-a-table-in-db2 :) – Biby Augustine Jan 13 '14 at 10:37
1

As Biby indicates, DB2 does not support this answer. And even if it did, why would you want to in this case? You only want one number, not multiple columns to hold several numbers. – WarrenT Jan 14 '14 at 02:16

score 0 · Answer 3 · answered Jan 14 '14 at 03:01

0

this will returns one phone number per customer id

SELECT 
    c.CustomerId, 
    c.Name,
    Min(p.Phone) Phone -- Or Max(p.Phone)
FROM Customers c
    LET OUTER JOIN Phones p on c.CustomeId = p.CustomeId
GROUP BY c.CustomerId, c.Name

answered Jan 14 '14 at 03:01

Jade

2,972
1
11
9

score 0 · Answer 4 · answered Jan 14 '14 at 07:21

0

Try this

Note: This is not tested code

SELECT 
       distinct Phones.Phone,
       Customers.CustomerId , 
       Customers.Name 

FROM Customers 
     INNER JOIN Phones ON Customers.CustomerId = Phones.CustomerId

answered Jan 14 '14 at 07:21

Siva

9,043
12
40
63

Nope, not going to work (and you really should have tested it). `DISTINCT` is going to do nothing in this case: the phone number is already unique per customer. – Clockwork-Muse Jan 14 '14 at 09:39

How to distinct duplicate rows(by field) in inner join clause

4 Answers4