CODEWITHSUNDEEP
sql
JeffreyLazo
JeffreyLazo

Reputation: 853

Tricky SQL Query (for me at least!)

I have this query:

SELECT  trackingno, COUNT(trackingno) AS NumberOfTimes 
FROM shippinginfo
GROUP BY trackingno HAVING (COUNT(trackingno)>1) 
ORDER BY NumberOfTimes DESC

It reports something along the lines of

Trackingno  |  NumberofTimes
12345       |  2
23456       |  2
34567       |  3

Is there a way to do the query so that I show other column data? 

Trackingno  |  OrderId
12345       |  oid1
12345       |  oid2
23456       |  oid3
23456       |  oid4
34567       |  oid5
34567       |  oid6

Basically I am looking for any DUPLICATE tracking numbers (trackingno) in the table, then report the tracking number and the orderid (orderid) for the duplicate tracking number rows so that I can contact the customers that have duplicate tracking numbers.

Upvotes: 0

Views: 53

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

SELECT Trackingno,orderId 
FROM shippinginfo
WHERE Trackingno IN (
                      SELECT trackingno
                      FROM shippinginfo
                      GROUP BY trackingno
                      HAVING COUNT(*) > 1

                     )

Upvotes: 1

zerkms
zerkms

Reputation: 255005

SELECT *
  FROM shippinginfo
 WHERE trackingno IN (

    SELECT trackingno
      FROM shippinginfo
  GROUP BY trackingno
    HAVING COUNT(*) > 1

)

So in the inner query you're selecting all trackingno that are duplicated, then in outer you select whatever you need

Upvotes: 2

Related Questions