.NET (2)
Database (71)
Delphi IDE (90)
Network (39)
Printing (3)
Strings (12)
VCL (83)
Windows with Delphi (280)
Exchange Links
About this site
| |
 |
SQL: Find duplicate rows in a table (with a primary key)
21 comments. Current rating: 
(13 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 | ||
 |  |
Comments:
| You are on page 1 of 2, other pages: [1] 2 | ||
| ||
|
good topic
| ||
| ||
|
Thank you.
| ||
| ||
|
Its a very helpful site. Thank you!!
Very apt and to the point stuff!!!! | ||
| ||
|
Brilliant! Thanks a lot. I have seen other websites on the same topic which go into pages of complicated SQL. This page gives a perfect solution in a few simple lines.
| ||
| ||
|
I forgot to say before that the above example also works great with MySQL.
| ||
| ||
|
Fantastic, made my job much easier
no if only Books online had these type of helpful hints... | ||
| ||
|
Excellent! I just now needed to know this for my project and the answer was clear and quick. God bless you all!
| ||
| ||
|
very usefull to new commers like me
| ||
| ||
|
very usefull to new commers like me
| ||
| ||
|
Thank you, I love this site, fast n easy steps XD
| ||
| ||
|
As we say in Great Britain - 'Does what it says on the tin'. Thanks !!
| ||
| ||
|
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) | ||
| ||
|
IT IS WORKING BUT HOW DO DO IF WE ARE LINKING 3 TABLES
| ||
| ||
|
Fantastic, made my job much easier ;God bless you all!
| ||
| ||
|
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
| ||
| You are on page 1 of 2, other pages: [1] 2 |
