.NET (2)
Database (71)
Delphi IDE (89)
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)
23 comments. Current rating: 
(12 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 2 of 2, other pages: 1 [2] | ||
| ||
|
Very nice topic for freshers
| ||
| ||
|
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: | ||
| ||
|
Very Helpfull . Thanks
| ||
| ||
|
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 | ||
| ||
|
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
| ||
| ||
|
great tip thanks
| ||
| ||
|
Dear Friends,
I just started programming with Delphi. My objective is as below I have a Table in MS ACCESS with say 10 fields. I wants to fetch a data based on the search criteria below The search is based on 3 or more fields. The given value for one filed will not be the same as the value in the table . We need to fetch nearest value from the table . The search string for the other values are as same as the table value. Please help how to fetch the data from the Table using ADO controls. | ||
| ||
|
Very good site
Thanku very much | ||
| You are on page 2 of 2, other pages: 1 [2] |
