Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

eliminating duplicates Expand / Collapse
Author
Message
Posted Thursday, June 19, 2008 4:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 11, 2009 10:34 PM
Points: 16, Visits: 49
Hello All,

i have a table with dupicate records.
I wish to delete the first row among the duplicate records and keep the others and the other way round.

How should have right the DELETE statement with top 1 clause in it.

Thanks in Advance..!!
Post #519716
Posted Thursday, June 19, 2008 6:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 5:13 AM
Points: 61, Visits: 216
Hi vish

u have not mentioned which version of sql server u r using because if u are using sql server 2000 then u will not be able to use Delete top clause.
Post #519792
Posted Thursday, June 19, 2008 9:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
Simply you can use DISTINCT keyword.

If you give some sample data's you will get lot of help from here.



karthik
Post #519972
Posted Thursday, June 19, 2008 9:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
Say for example,

create table #Emp
(
Eno int,
Ename varchar(5)
)

insert into #Emp
select 1,'AA'
union all
select 1,'AA'
union all
select 2,'BB'
union all
select 3,'CC'

select distinct * into #FinalResult
from #Emp

will give you the expected result.





karthik
Post #519977
Posted Thursday, June 19, 2008 9:59 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,840, Visits: 3,841
How you do this depends on a couple of factors. How many rows are in your table? How many rows are dupes that need to be deleted?



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #519987
Posted Thursday, June 19, 2008 9:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 11, 2009 10:34 PM
Points: 16, Visits: 49
Hi All,

Let me give my problem description in detail.
I use SQL server 2000.
I have a table 'TAB' with 3 coulumns 'Name','Age','Sex'
and there is no primary key.

I have multiple duplicate data in my table. for eg..

ABC 24 M
ABC 24 M
LMN 27 M
LMN 27 M
LMN 27 M
PQRS 25 F
XYZ 24 M
XYZ 25 M

Now i would wish to 'DELETE' the 1st original row ( Row no 1,3,6,7)
and keep the other duplcates from above data.
Now, If there is a row without any dupicates present.. it will be deleted ( for eg Row no 6)

The condition is i dont want to go for intermediate tables or have any
additional identity column.

Please help..!!

Thanks folks
Post #520317
Posted Thursday, June 19, 2008 10:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
vish,
I don't see any numbers assigned to the rows.

How can I tell which ones are 1,3,6,7?
Post #520323
Posted Thursday, June 19, 2008 10:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 11, 2009 10:34 PM
Points: 16, Visits: 49
Hi,
There no row numbers associated.. i just wanted to make it clear..
through that example.

Thanks
Post #520326
Posted Thursday, June 19, 2008 10:52 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
vish (6/19/2008)i just wanted to make it clear..

You did not succeed.

For me these lines:
LMN 27 M
LMN 27 M
LMN 27 M
look absolutely identical.
If it's all you've go in table they are identical for SQL Server as well.

Open this table in EM and try to remove one of the rows.
I will not do because of "not enough key information".

If you try to delete it from QA you need to specify which row(s) to delete:
DELETE
FROM Table
WHERE ... = 'LMN' AND ... = 27 AND ''' = 'M'
AND ?????

Without specifying what do you mean by ????? all 3 rows will be deleted.
So, you need to define the criteria for the deletion more specifically.
Post #520329
Posted Thursday, June 19, 2008 11:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 11, 2009 10:34 PM
Points: 16, Visits: 49
hi sergiy,

Thats right..
Those rows are practically identical...
So the point is we need to have some condition which will distinguish them from each other...

So i feel its quite impossible under such given conditions.

I think i get the blackhole.

Thanks to all for your responses..!!
vish
Post #520333
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse