SQL Server, 2000 - How to delete duplicated rows if the primary key is multiple columns

For this the best way to do is Create another table with same structure along with primary key on the column you want and then Select DISTINCT Rows from the first table and insert them into the new table.

This is the only way you can do this.

Or directly do a Select DISTINCT from the table into new table name and then drop the source and rename the new table to old table name. for example

Select Distinct Deail_Number, Order_Number, ValueDate, Col4, Col5,... Into NewTableName from DuplicatedValueTableName.

Drop the DuplicatedValueTableName

and then rename the NewTableName to OldTableName

But what if i have 10 columns, and the primary key (the ones i need to distinctly select) are only 3 ???
How do i write my select statement?

-----------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT     IKey, Deal_Number, Deal_Time, Order_Number, Order_Time, Order_Type, CompanyID, Price, QTY, Investor_Number, [User], Status, 
                      Main_Forward_Market_Deal_Date, Main_Deal_Number, Investment_Company_Number, ValueDate
FROM         Broker_TRADE
-----------------------------------------------------------------------------------------------------------------------------------------------------------

Select allOpen in new window

SELECT   Min(IKey), Deal_Number, Min(Deal_Time), Order_Number, Order_Time, Order_Type, CompanyID, Price, QTY, Investor_Number, [User], Status,
                      Main_Forward_Market_Deal_Date, Main_Deal_Number, Investment_Company_Number, ValueDate
FROM         Broker_TRADE
group by Deal_Number, Order_Number, ValueDate

Please add the Min or Max function as i have used according to what you need. The query will go with group by your distinct columns. Dont put min or max to your group by cols.

how big is the table, how many rows?

Hi imitchie,
The table has around 100,000 rows.
It has the columns: IKey, Deal_Number, Deal_Time, Order_Number, Order_Time, Order_Type, CompanyID, Price, QTY, Investor_Number, [User], Status, Main_Forward_Market_Deal_Date, Main_Deal_Number, Investment_Company_Number, ValueDate

Do I execute your code now?

yup, that'd do it

Hi imitchie!!
It worked perfectly!! Thank you very much. This was a relief :)

PERFECT!!