Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-18 : 09:55:29
|
| Lloyd writes "I have a large table with duplicate- with name(varchar), email(varchar), total(decimal), ID(int).I want to delete all the duplicates keeping only the records with the highest total for each unique email address.Many Thanks, This is really bugging me.Lloyd" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-18 : 10:40:11
|
| DELETE FROM MFROM myTable M LEFT JOIN(SELECT email, Max(Total) AS MaxTotal FROM myTable GROUP BY email) AON (M.email=A.email AND M.Total=A.MaxTotal)WHERE A.email Is Null AND A.MaxTotal Is NullThe inner SELECT query finds the maximum total for each email address. The LEFT JOIN includes all of the rows from your table, and the WHERE clause restricts the deleted rows to the ones that don't appear in the subquery. |
 |
|
|
|
|
|
MAX Value with duplicate fields