Find duplicate rows in a table (no primary key)

Delphi
.NET (2)
Database (72)
Delphi IDE (90)
Network (39)
Printing (3)
Strings (12)
VCL (83)
Windows with Delphi (243)

Exchange Links

About this site

Links to us

See also:

Find duplicate rows in a table (no primary key)

18 comments. Current rating: 4 stars

(9 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

Comments:

You are on page 1 of 2, other pages: [1] 2

2006-02-21, 23:11:47

anonymous from India

2006-03-27, 01:12:11

sannyshoney@yahoo.com from India

Thanks for such helpful and worthy info. It solved my purpose in need.

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:19:20

anonymous from India

2006-05-23, 05:29:27

anonymous from India

2006-07-05, 21:17:43

anonymous from United States

how to identify when we want to compare uniqueness of multiple columns

eg identifying duplicate combination of country,state,city.

2006-08-09, 14:04:31

anonymous from United States

Very helpful!

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-02-19, 18:10:36

gudmewarshivnath@gmail.com

SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress,dtmModified
HAVING COUNT(*) > 1

2007-03-18, 16:26:18

Anoop from Australia

2007-07-19, 05:20:54

anonymous from Switzerland

Use full

2007-08-08, 10:40:13

anonymous from United States

The correct example is this

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

Should be having not where for Oracle.

Bob Caputo
who@elknet.net

2007-12-18, 00:12:48

anonymous from India

SELECT distinct cs1.cs_cd,cs1.cs_name,cs1.main_loc_cd,cs1.sub_loc_cd,cs1.loc_cd
FROM MAST_CS cs1,MAST_CS cs2
WHERE cs1.cs_cd <> cs2.cs_cd
AND cs1.cs_name LIKE cs2.cs_name
and cs1.cs_type='C';

2008-01-22, 07:04:15

anonymous from Germany

used it in MySQL and it worked great. very helpful, thanks

2008-02-26, 22:29:34

anonymous from United States

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?

You are on page 1 of 2, other pages: [1] 2


	Search: