DelphiFAQ Home Search:

Find duplicate rows in a table (no primary key)

 

comments27 comments. Current rating: 4 stars (8 votes). Leave comments and/ or rate it.

Question:

I have a table of city names and need to find duplicate entries. The table does not have a primary key, so the duplicate rows are entirely identical.

Answer:

You can use the GROUP clause in your SELECT statement. Below are 3 different attempts. Try the first one first. It may not work with your DBMS but if it works, it's most convenient. The second one is an ok choice as it returns all rows but the duplicate rows in the 'See Also' box.

The last version returns all city names with a count value and you manually have to look for those with a value > 1. If you need to do this more often, you should consider creating a VIEW or a temporary table with this query and then do a SELECT on this VIEW/ temp table.

In Microsoft SQL-Server, you can use the HAVING clause, as shown at the bottom.
And: If you do have a primary key defined, see the other tip mentioned at the top. This would work in that situation, but using the unique ID is better.

// Return all duplicate cities and how often they appear
// Works with ORACLE

select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1


// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL

select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc


// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear

select city_name, count(city_name) as cnt
from areas
group by 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
2006-02-21, 23:11:47
anonymous from India  
rating
2006-05-15, 09:07:48
anonymous from Czech Republic  
it's better
SELECT A.ID, B.ID
FROM
T1 A
INNER JOIN T1 B ON (A.F1=B.F1 AND A.F2 AND B.F2 AND ...)
WHERE A.ID<B.ID
2006-05-23, 05:29:27
anonymous from India  
rating
2007-01-15, 01:52:35
anonymous from India  
When you want to find duplicate combination of multiple columns,
group by is the best approach.

SELECT col1, col2,col3,count(col3)--put the last column of duplicate combination in Count()
FROM table1
GROUP BY col1,col2,col3

Here as col1,col2,col3 combination will be unique as they are grouped by GROUP BY clause and count() returns the count. We can add a HAVING Clause to filter combinations for whom the count() is > 1

SELECT col1, col2,col3,count(col3)--put the last column of duplicate combination in Count()
FROM table1
GROUP BY col1,col2,col3
HAVING COUNT(col3) >1
2007-07-19, 05:20:54
anonymous from Switzerland  
Use full
2008-01-22, 07:04:15
anonymous from Germany  
rating
used it in MySQL and it worked great. very helpful, thanks
2008-02-26, 22:29:34
anonymous from United States  
rating
I have a slightly different need.

I have a table that carries multiple rows for a single key.

Example:
Two cities in same state. (Cities Marlboro, Middleboro in State of Califernia)

My need is to show one row per state and cancatenate cities in that row, e.g.,

Califernia, Marlboro and Middleboro

Any suggestions on how to do this?
2008-04-04, 04:36:27
anonymous from India  
I have a slightly different need.

I have a table that carries multiple rows for a single key.

Example:
Two cities in same state. (Cities Marlboro, Middleboro in State of Califernia)

My need is to show one row per state and cancatenate cities in that row, e.g.,

Califernia, Marlboro and Middleboro

Any suggestions on how to do this?


Your Solution can be:

SELECT state1 . * , state2.name
FROM `state` state1
LEFT JOIN state state2 ON state1.statename = state2.statename
WHERE state1.city <> state2.city
GROUP BY state1.statename
2008-05-06, 03:43:32
anonymous from India  
Good ,It worked for me too. thanks.
2008-06-24, 14:18:59
anonymous  
Can someone help me with my problem?
My table looks like this

CID FNAME MIN MAX COM
A OP1 0 23 5
A OP1 24 35 2
A OP1 36 99 1

A OP2 0 23 5
A OP2 24 35 2
A OP2 36 99 0

A OP3 0 23 5
A OP3 24 35 2
A OP3 36 99 1

B OP1 0 23 9
B OP1 24 99 2

B OP2 0 23 9
B OP2 24 99 2

B OP3 0 23 7
B OP3 24 35 3
B OP3 36 99 1


The expected duplicate results i need are:
A OP1
A OP3
B OP1
B OP2

2008-07-08, 07:35:23
anonymous from Oulu, Finland  
rating
Thanks guys, really helpful.
2008-07-19, 08:17:35
[hidden]  
This would also work:

SELECT
city_name,
COUNT(city_name) AS NumOccurrences
FROM
areas
GROUP BY
city_name
HAVING ( COUNT(city_name) > 1 )

// By =IceBurn= //
2008-12-16, 05:39:54
anonymous from Malaysia  
Vary Good Support and Help
2009-01-08, 11:08:33
anonymous from United States  
Add a sequential primary key by using the ALTER TABLE statement

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD column_name column-definition;

For example:

ALTER TABLE supplier
ADD supplier_name varchar2(50);

This will add a column called supplier_name to the supplier table.

2009-09-08, 08:00:48
anonymous from India  



Keywords:
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: