<div>
You can use a Row Number or a RANK function:<br />
<a href="http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx" rel="ugc">http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx</a><br />
<a href="http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx" rel="ugc">http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx</a><br />
<br />
if you RANK them just in the &quot;partition by&quot; field list ALL the fields then delete where Rank is &lt;&gt;&nbsp;'1'<br />
<br />
Similar for Row 
</div>


You can use a Row Number or a RANK function:
http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx [http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx]
http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx [http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx]

if you RANK them just in the "partition by" field list ALL the fields then delete where Rank is <> '1'

Similar for Row

<div>
Delete &nbsp;from MyTable where ID Not IN<br />
(<br />
&nbsp; &nbsp; &nbsp;select &nbsp;max(ID) from MyTable Group By Duplicate Colum1, Duplicate Column2,Duplicate Column 3<br />
)<br />
Table must have identity column which would identify the duplicate records. Here in MyTable ID as identity column &nbsp;and duplicate columns are duplicate columns 1,2 &amp;&nbsp;3 (very obvious to know...!!!)<br />
Also refer : &nbsp; <a href="http://support.microsoft.com/kb/139444" rel="ugc">http://support.microsoft.c<wbr />om/kb/1394<wbr />44</a><br />
&nbsp;<br />

</div>


Delete  from MyTable where ID Not IN
(
     select  max(ID) from MyTable Group By Duplicate Colum1, Duplicate Column2,Duplicate Column 3
)
Table must have identity column which would identify the duplicate records. Here in MyTable ID as identity column  and duplicate columns are duplicate columns 1,2 & 3 (very obvious to know...!!!)
Also refer :   http://support.microsoft.com/kb/139444 [http://support.microsoft.com/kb/139444]
 


<div>
<div class="content wysiwyg-content">
I have 6 identical rows of 4 columns table in sql server 2005<br />
I like to have a script to delete the duplicate rows<br />
Is there a way to list rowid ( oracle concept) along with the columns to delete one of the duplicate rows<br />
<br />
Thanks
</div>
</div>


I have 6 identical rows of 4 columns table in sql server 2005
I like to have a script to delete the duplicate rows
Is there a way to list rowid ( oracle concept) along with the columns to delete one of the duplicate rows

Thanks

how to delete duplicate rows in table sql server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.