Solved

SQL Query to display duplicates ?

Posted on 2016-11-22
6
49 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select values in a row based on values in another row in sql 4 26
SQL query and VBA 5 46
Stored Proc - Rewrite 42 59
RAISERROR WITH NOWAIT 2 17
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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