Fetching Duplicate Rows

Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in AdvanceI have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

Can you paste some DDL. Are all the columns for the duplicated rows same ? Is there any date time stamp ? You could find some help Here (http://www.sqlteam.com/item.asp?ItemID=3331)|||Try something like:

Select * from Table
Where {Key Fields} IN
(select {key Fields} from Table
group by {Key Fields}
Having Count(*) >1)

Where Table is your Table/Query and {Key Fields} is the list of fields you want to search for duplicates on

HTH

Marp

[
QUOTE]Originally posted by d_kishan
Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in Advance [/QUOTE]|||You say you want to delete the duplicated rows, but I bet what you really want is to delete all but one of each duplicated row. Your first step is to add a primary key. Otherwise, there is no way to discern which of the duplicated rows to retain and your only option will be to select DISTINCT into a new table and then replace your old table.|||If you want to know how many duplicate rows you have per your Key criteria,

do this

[Select Key1,Key2,..Keyn, count(*)
from Table
group by {Key1,Key2,...Keyn}
Having Count(*) >1)

Features in SQLCE 3.5 BETA 2: TOP, DISTINCT, SUB QUERIES?

Are these implemented in Beta 2:

TOP

DISTINCT

sub queries in SELECT clause?

sub queries in FROM clause? (as in ... FROM (SELECT ...)

From the beta 2 doc:

SQL Server Compact 3.5 support for transact-SQL statements has been extended as follows:

Nested query in SELECT FROM clause

TOP