REMOVE DUPLICATE ROWS

[Diego Rey]

Hi everyone.

How can I get the unique row from a table which contains multiple rows
that have exactly the same values.

example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )

insert into test values (1,2,3)
insert into test values (1,2,3)

i want to remove whichever of the rows but I want to retain a single
row.

TIA

Diego

[John Bell]

Hi

There are several ways of doing this... You can select distinct rows into a
temporary table (See DISTINCT in books online), clear out your main table
and then re-populate it. If you have an differentiating column then you can
use that to delete rows that are not (say) the minimum value of that
column, or you could stop rows being put in the table in the first place by
having a unique index or primary key over the columns that should be
distinct, or using a not exists clause when inserting the data.

E.g.
..
SELECT DISTINCT *
INTO #SimpleExample
FROM Test

TRUNCATE TABLE TEST

INSERT INTO TEST ( c1, c2, c3 )
SELECT * FROM #SimpleExample

As there is no AS in a CREATE TABLE statement you will have problems with
this DDL, there is also no information regarding PKs etc which would have
been useful. See http://www.aspfaq.com/etiquette.asp?id=5006
John

"Diego Rey" <di******@yahoo.com> wrote in message
news:e0**************************@posting.google.c om...

Hi everyone.

How can I get the unique row from a table which contains multiple rows
that have exactly the same values.

example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )

insert into test values (1,2,3)
insert into test values (1,2,3)

i want to remove whichever of the rows but I want to retain a single
row.

TIA

Diego

[Erland Sommarskog]

John Bell (jb************@hotmail.com) writes:

As there is no AS in a CREATE TABLE statement you will have problems with
this DDL, there is also no information regarding PKs etc

Obviously, if he has identical rows in his table, there is no primary key.

Which all good tables in relational database is supposed to have, and thus
this explains why this operation is not a trivial one to perform. You are
simply not supposed to wind up in this situation.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anith Sen]

>> Obviously, if he has identical rows in his table, there is no primary

key...Which all good tables in relational database is supposed to have,

Is that a "relational heresy" or a "relational orthodoxy" or an "attempt to
appease the ideas of relational theory" ?( ..kidding :-) )

--
Anith