Solved

SQL Query to display duplicates ?

Posted on 2016-11-22
6
44 Views
Last Modified: 2016-11-23
Hi,
I have a table of Customers.  In some cases the names are the same for various customer ID's.  For example (see below),  Clinton is attached to 3 customers:
CUSTID  NAME
------|----------
39    | Carter   
40    | Reagan   
41    | Carter     
42    | Clinton  
44    | Clinton    
45    | Clinton

Open in new window

I would like to write a query that will give me a list of the duplicates (and only the duplicates) ordered by name, with a result something like:
CUSTID  NAME
------|----------
39    | Carter   
41    | Carter     
42    | Clinton  
44    | Clinton    
45    | Clinton 

Open in new window

Any suggestions?
0
Comment
Question by:Rob Rudloff
6 Comments
 
LVL 15

Accepted Solution

by:
mcmonap earned 250 total points
ID: 41897936
In it's simplest form something like this:
SELECT
	*
FROM
	customers
WHERE
	[name] IN(
		SELECT
			[Name]
		FROM
			customers
		GROUP BY
			[name]
		HAVING
			COUNT(1) > 1)

Open in new window

0
 

Author Comment

by:Rob Rudloff
ID: 41898108
Ah yes -- I always forget about HAVING and then when I remember it, I can't seem to make it work.  (I'll read up on it)  
I will employ your suggestion.  Thanks!  
(I found another example using a join to the same table.  If I can get that to work, I'll post it here.)
0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41898227
Try this ...easiest one..

--

SELECT * FROM 
(
       SELECT * , COUNT(*) OVER (PARTITION BY name) rnk FROM customers
)k
WHERE rnk > 1

--

Open in new window


Hope it helps !
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 32

Expert Comment

by:awking00
ID: 41899551
What dbms (and version) are you using?
0
 

Author Comment

by:Rob Rudloff
ID: 41899748
This is SQL Server 2008 R2.
0
 

Author Closing Comment

by:Rob Rudloff
ID: 41899752
Thanks!
I will work with both solutions, so that I can understand -- I am new-ish to SQL and want to learn from these examples you've posted.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question