2

Possible Duplicate:
SQL - How can I remove duplicate rows?

I have a table that's structured like this:

create table MnA
(id         int PRIMARY KEY IDENTITY    not null,
symbol      nvarchar(4)                 not null,
direction   nvarchar(4)                 not null,
start_dt    nvarchar(5)                 not null,
end_dt      nvarchar(5)                 not null,
start_doy   int                         not null,
end_doy     int                         not null,
avg_price   int                         not null,
min_price   int                         not null,
max_price   int                         not null,
avg_percent int                         not null,
min_percent int                         not null,
max_percent int                         not null,
history     text                        not null,
percent_hit int                         not null,
aw_length   int                         not null,
diff        int                         not null,
date_change int                         not null)

I would like to delete rows that have similarities.

If the row has the same symbol, direction, start_doy and diff then I want to keep the one with the highest avg_percent.

How would I accomplish this?

Community
  • 1
  • 1
Mike
  • 2,299
  • 13
  • 49
  • 71
  • 3
    Please show some sample duplicate rows and how you determine which row to keep. Also, specifying the *version* of SQL Server is useful. – Aaron Bertrand Aug 21 '12 at 22:43
  • Also not sure why the answer from @TimSchmelter was down-voted. It's as good a guess as any as to what breaks the tie... – Aaron Bertrand Aug 21 '12 at 22:59

1 Answers1

4
DELETE target 
FROM MnA target INNER JOIN MnA temp 
     ON (target.symbol = temp.symbol 
         AND target.direction = temp.symbol
         AND target.start_doy = temp.start_doy
         AND target.diff = temp.diff
         AND target.id != temp.id 
         AND temp.avg_percent > target.avg_percent);

DEMO (sqlfiddle).

João Silva
  • 89,303
  • 29
  • 152
  • 158