I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

April Edition
Subscribe now! It's Free!
Enter your email:

   ► KBRole-Based T...DBA & DataANSI SQL Scr...   Print This     
  From the February 2016 Issue of Prestwood eMag
 
DBA ANSI SQL Scripting:
Finding Duplicate Values Using SQL
 
Posted 17 years ago on 11/19/2002 and updated 12/16/2008
Take Away: To find duplicate values in the same field, you need to use an alias with the same table twice.
 Tags: SQL , duplicates

KB100061



Finding Duplicate Values

To find duplicate values in the same field, you need to use an alias with the same table twice. Perhaps seeing this is easier than explaining it. Notice in the following example that the customer table is added twice to the "from" clause in the SQL statement. Then in the where clause both c1 and c2 aliases are used to refer to the same table. First we use both aliases to mach up the City column. Then we use both aliases to exclude the primary key.

Finding Duplicates in a single column:
select c1.*
from "customer.db" c1, "customer.db" c2
where c1."City" = c2."City"
and c1."Customer No" <> c2."Customer No"
Finding Duplicates in Multiple Fields:
select c1.*
from "customer.db" c1, "customer.db" c2
where c1."City" = c2."City"
and c1."State" = c2."State"
and c1."Customer No" <> c2."Customer No"

For multiple column primary key fields, you would add a line similar to the last line for each column.


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1196A1
Enter key:
KB Post Contributed By Mike Prestwood:

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Visit Profile

 KB Article #100061 Counter
7647
Since 4/2/2008
-
   Contact Us!
 
PrestwoodBoards.com was developed and is maintainted by me. Do you have a question or suggestion? Do you see a problem? Contact me now. My goal is to build an ad-free and spam-free source of I.T. information with many contributers (ok to promote your website/company in your bio). Yes, my company Prestwood IT Solutions is mentioned in my bio which shows with every post, but you can contribute and promote your pet project too!

2,900 People Online Now!!  
Sign In to see who's online now!  Not a member? Join now. It's free!
Show more stats...


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech