Solved

SQL Query to display duplicates ?

Posted on 2016-11-22
6
23 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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now