DelphiFAQ Home Search:

SQL: Find duplicate rows in a table (with a primary key)

 

comments24 comments. Current rating: 5 stars (10 votes). Leave comments and/ or rate it.

Question:

I have a table of city names and need to find duplicate entries. The table does have a primary key called CITY_ID, so the duplicates will have different CITY_ID values but identical CITY_NAME values.

Answer:

If you indeed have a primary key then you need two cursor instances (c1, c2) as the following example shows. The query requires that ID #1 is smaller than ID #2 otherwise all pairs would be returned twice (2,3) and (3,2) or, if you don't even require that c1 <> c2, .. well, try that out for yourself.

If you do not have a primary key defined, see the other tip mentioned in the 'See Also' box.

Note:
In Microsoft SQL-Server, you can use the HAVING clause, as shown at the bottom.

// return all pairs of city IDs that have the same city name

select c1.city_id, c2.city_id, c1.city_name
from cities c1, cities c2
where c1.city_id < c2.city_id and c1.city_name = c2.city_name


// version for Micrsoft's MSSQL Server
// make use of the HAVING clause
select city_name
from areas
group by city_name
having count(*) > 1

Content-type: text/html

Comments:

You are on page 1 of 2, other pages: [1] 2
2007-04-05, 13:49:22
Dog_Cheez from United States  
rating
I forgot to say before that the above example also works great with MySQL.
2007-06-11, 22:21:22
anonymous from Australia  
rating
Fantastic, made my job much easier

no if only Books online had these type of helpful hints...
2007-06-14, 05:34:18
[hidden] from Tallahassee, United States  
rating
Excellent! I just now needed to know this for my project and the answer was clear and quick. God bless you all!
2007-09-24, 03:29:22
anonymous from Hong Kong  
Thank you, I love this site, fast n easy steps XD
2007-10-09, 01:52:35
anonymous from United Kingdom  
rating
As we say in Great Britain - 'Does what it says on the tin'. Thanks !!
2007-10-09, 05:50:59
JRyan from Oxford, United Kingdom  
rating
There's a 100 ways to solve a problem. Here's another alternative:-

select q1.CITY_NAME
from CITIES q1
where 1 < (select count(*)
from CITIES q2
where q1.CITY_NAME = q2.CITY_NAME)
2007-10-26, 06:51:49
anonymous from Zimbabwe  
IT IS WORKING BUT HOW DO DO IF WE ARE LINKING 3 TABLES
2008-03-25, 06:49:35
anonymous from Bombay, India  
rating
Fantastic, made my job much easier ;God bless you all!


2008-04-29, 03:36:08   (updated: 2008-04-29, 03:41:31)
anonymous from India  
how to retrieve the primary key id if there is more than one columns in a duplicate result query.actually i retrieve the other columns that are duplicate but now i want to retrieve the ids for that rows.pls help me
2008-05-28, 09:57:22
anonymous from Canada  
Godlike! Thank you.
2008-06-17, 04:23:14
anonymous from India  
rating
Very nice topic for freshers
2008-08-18, 01:12:00
anonymous from China  
Try this Code:

SELECT DISTINCT column1 from table
group by column1 HAVING count(column1) > 1
This groups your id's and only shows the id's that have more than 1 occurence in your table.

Hope this helps!


Keywords:
2008-09-09, 03:40:39
anonymous from Pakistan  
rating
Very Helpfull . Thanks
2008-10-14, 16:28:57
Geekette  
rating
this code has been so helpful to me, I have been converting data from one table into another DB. I use it just about every day!
Thanx
2008-10-15, 03:37:15
anonymous  
Hi, how do i list all my salesman-customers pairs who made the largest number of transactions together, whereby the customer has never complain against the salesman? That is, i have a lot of pairs, but i want to select all sellers to be distinct with their most popular customer
You are on page 1 of 2, other pages: [1] 2

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: