how to eliminate duplicate rows?

[dave]

Hi
I have one table with two field in sql server 2000.
Table data looks like this:

Field1 Field2

Admin Active
Admin Passive
Sales Active
Sales Passive
Systems Active
Systems Active
Admin Passive
Sales Passive

Can any one suggest how to write sql query to eliminate
duplicates records(duplicate rows) from the table???
Thanx
dave

[Aaron [SQL Server MVP]]

SELECT column1, column2 FROM table GROUP BY column1, column2

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"dave" <anonymous@discussions.microsoft.com> wrote in message
news:2251b01c45ca4$c2611230$a001280a@phx.gbl...

> Hi
> I have one table with two field in sql server 2000.
> Table data looks like this:
>
> Field1 Field2
>
> Admin Active
> Admin Passive
> Sales Active
> Sales Passive
> Systems Active
> Systems Active
> Admin Passive
> Sales Passive
>
> Can any one suggest how to write sql query to eliminate
> duplicates records(duplicate rows) from the table???
> Thanx
> dave
>

[dave]

Thanx Aaron
Result shows distinct records that I need to keep in the
table. But how can I delete the rest of them?
Dave

>-----Original Message-----
>SELECT column1, column2 FROM table GROUP BY column1,

column2

>
>--
>[url]http://www.aspfaq.com/[/url]
>(Reverse address to reply.)
>
>
>
>
>"dave" <anonymous@discussions.microsoft.com> wrote in

message

>news:2251b01c45ca4$c2611230$a001280a@phx.gbl...

>> Hi
>> I have one table with two field in sql server 2000.
>> Table data looks like this:
>>
>> Field1 Field2
>>
>> Admin Active
>> Admin Passive
>> Sales Active
>> Sales Passive
>> Systems Active
>> Systems Active
>> Admin Passive
>> Sales Passive
>>
>> Can any one suggest how to write sql query to eliminate
>> duplicates records(duplicate rows) from the table???
>> Thanx
>> dave
>>

>
>
>.
>

[Aaron [SQL Server MVP]]

Oh, you actually want to delete them? My first suggestion would be to
create a second table, insert the results of the GROUP BY, then add a
primary key. Drop the old table, rename the new one to the old name.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)



"dave" <anonymous@discussions.microsoft.com> wrote in message
news:21f4401c45ca9$d72e4a20$a301280a@phx.gbl...

> Thanx Aaron
> Result shows distinct records that I need to keep in the
> table. But how can I delete the rest of them?
> Dave

[dave]

any other alternatives?
cant do it using single sql statment????
Dave

>-----Original Message-----
>Oh, you actually want to delete them? My first

suggestion would be to

>create a second table, insert the results of the GROUP

BY, then add a

>primary key. Drop the old table, rename the new one to

the old name.

>
>--
>[url]http://www.aspfaq.com/[/url]
>(Reverse address to reply.)
>
>
>
>"dave" <anonymous@discussions.microsoft.com> wrote in

message

>news:21f4401c45ca9$d72e4a20$a301280a@phx.gbl...

>> Thanx Aaron
>> Result shows distinct records that I need to keep in

the

>> table. But how can I delete the rest of them?
>> Dave

>
>
>.
>

[Aaron [SQL Server MVP]]

No, because you can't tell a DELETE statement to delete this row:

Admin Passive

But not this row:

Admin Passive

Because it can't tell them apart. This is one of the dangers you face when
you neglect to choose a primary key.

Another way to do it would be to add an IDENTITY column to the table, and
then see [url]http://www.aspfaq.com/2431[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"dave" <anonymous@discussions.microsoft.com> wrote in message
news:225d401c45ccc$05b77750$a001280a@phx.gbl...

> any other alternatives?
> cant do it using single sql statment????
> Dave
>

> >-----Original Message-----
> >Oh, you actually want to delete them? My first

> suggestion would be to

> >create a second table, insert the results of the GROUP

> BY, then add a

> >primary key. Drop the old table, rename the new one to

> the old name.

> >
> >--
> >[url]http://www.aspfaq.com/[/url]
> >(Reverse address to reply.)
> >
> >
> >
> >"dave" <anonymous@discussions.microsoft.com> wrote in

> message

> >news:21f4401c45ca9$d72e4a20$a301280a@phx.gbl...

> >> Thanx Aaron
> >> Result shows distinct records that I need to keep in

> the

> >> table. But how can I delete the rest of them?
> >> Dave

> >
> >
> >.
> >

[Jeff Cochran]

On Sun, 27 Jun 2004 21:54:41 -0700, "dave"
<anonymous@discussions.microsoft.com> wrote:

>any other alternatives?

If you have no primary key, how would you identify *which* is the
duplicate? :)

>cant do it using single sql statment????

The best (safest) method is what Aaron suggested. SELECT DISTINCT to
a new table, drop the old table and rename the new one.

Naturally, this assumes this table has no interdependencies with any
other tables, but it would be tough for any other table to use this
one without a primary key anyway. Your database design may change the
way you need to accomplish this. In the future, you could use a
primary key consisting of the two columns in order to ensure no
duplication exists.

Jeff

>>-----Original Message-----
>>Oh, you actually want to delete them? My first

>suggestion would be to

>>create a second table, insert the results of the GROUP

>BY, then add a

>>primary key. Drop the old table, rename the new one to

>the old name.

>>
>>--
>>[url]http://www.aspfaq.com/[/url]
>>(Reverse address to reply.)
>>
>>
>>
>>"dave" <anonymous@discussions.microsoft.com> wrote in

>message

>>news:21f4401c45ca9$d72e4a20$a301280a@phx.gbl.. .

>>> Thanx Aaron
>>> Result shows distinct records that I need to keep in

>the

>>> table. But how can I delete the rest of them?
>>> Dave

>>
>>
>>.
>>

[dave]

Thanx Aaron
I added auto number primary key and referred that article
tht you suggested.
It solved my problem exactly what I was looking for.
Once again,
Thanx
Dave

>-----Original Message-----
>No, because you can't tell a DELETE statement to delete

this row:

>
>Admin Passive
>
>But not this row:
>
>Admin Passive
>
>Because it can't tell them apart. This is one of the

dangers you face when

>you neglect to choose a primary key.
>
>Another way to do it would be to add an IDENTITY column

to the table, and

>then see [url]http://www.aspfaq.com/2431[/url]
>
>--
>[url]http://www.aspfaq.com/[/url]
>(Reverse address to reply.)
>
>
>
>
>"dave" <anonymous@discussions.microsoft.com> wrote in

message

>news:225d401c45ccc$05b77750$a001280a@phx.gbl...

>> any other alternatives?
>> cant do it using single sql statment????
>> Dave
>>

>> >-----Original Message-----
>> >Oh, you actually want to delete them? My first

>> suggestion would be to

>> >create a second table, insert the results of the GROUP

>> BY, then add a

>> >primary key. Drop the old table, rename the new one

to

>> the old name.

>> >
>> >--
>> >[url]http://www.aspfaq.com/[/url]
>> >(Reverse address to reply.)
>> >
>> >
>> >
>> >"dave" <anonymous@discussions.microsoft.com> wrote in

>> message

>> >news:21f4401c45ca9$d72e4a20$a301280a@phx.gbl...
>> >> Thanx Aaron
>> >> Result shows distinct records that I need to keep in

>> the

>> >> table. But how can I delete the rest of them?
>> >> Dave
>> >
>> >
>> >.
>> >

>
>
>.
>