• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

SQL Query to display duplicates ?

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
Rob Rudloff
Asked:
Rob Rudloff
2 Solutions
 
mcmonapCommented:
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
 
Rob RudloffIT Development SpecialistAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
awking00Commented:
What dbms (and version) are you using?
0
 
Rob RudloffIT Development SpecialistAuthor Commented:
This is SQL Server 2008 R2.
0
 
Rob RudloffIT Development SpecialistAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now