I think this will work, I found it on BOL.
Eliminating Duplicates with DISTINCT
The DISTINCT keyword eliminates duplicate rows from the results of a SELECT
statement. If DISTINCT is not specified, all rows are returned, including
duplicates. For example, if you select all the author IDs in titleauthor
without DISTINCT, the following rows are returned (with some duplicate
listings):
USE pubs
SELECT au_id
FROM titleauthor
Here is the result set:
au_id
-----------
172-32-1176
213-46-8915
213-46-8915
238-95-7766
267-41-2394
267-41-2394
274-80-9391
409-56-7008
427-17-2319
472-27-2349
486-29-1786
486-29-1786
648-92-1872
672-71-3249
712-45-1867
722-51-5454
724-80-9391
724-80-9391
756-30-7391
807-91-6654
846-92-7186
899-46-2035
899-46-2035
998-72-3567
998-72-3567
(25 row(s) affected)
With DISTINCT, you can eliminate duplicates and see only the unique author
IDs:
USE pubs
SELECT DISTINCT au_id
FROM titleauthor
Here is the result set:
au_id
-----------
172-32-1176
213-46-8915
238-95-7766
267-41-2394
274-80-9391
409-56-7008
427-17-2319
472-27-2349
486-29-1786
648-92-1872
672-71-3249
712-45-1867
722-51-5454
724-80-9391
756-30-7391
807-91-6654
846-92-7186
899-46-2035
998-72-3567
(19 row(s) affected)
Important The output for statements involving DISTINCT depends on the
collation of the column or expression on which the DISTINCT is applied. For
more information about the effects of different collations, see SQL Server
Collation Fundamentals.
For the DISTINCT keyword, null values are considered to be duplicates of
each other. When DISTINCT is included in a SELECT statement, only one NULL
is returned in the results, regardless of how many null values are
encountered.
Jorge Herrera
Chevy Chase Bank
-----Original Message-----
From: Fang, Elaine [mailto:Elaine.[Email Address Removed] Monday, August 12, 2002 2:59 PM
To: [Email Address Removed] how to delete a duplicate row?
Hi list,
For a table w/o PK and unique index, how to delete a duplicate row? Is there
any system defined column (e.g. rowid etc) that I can use?
Thanks!
Elaine
---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] mail is to be used for Bank business purposes only. Confidential
information should not be sent via email as there is no expectation of
privacy or integrity of the message through the internet.
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This email message has been scanned for the presence of
computer viruses.
Chevy Chase Bank
**********************************************************************
MS Sql Server LazyDBA home page