Removing duplicate rows with higher value

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Removing duplicate rows with higher value

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-14 : 07:09:34

Hi

I have a stored procedure that runs a query to insert summary information into a table. However, it was run three times accidently, meaning there are three lots of summary information.

LetterCode CycleName Mail Date NumberMailed
028 ABB0101 Feb 9 2005 1871
028 ABB0101 Feb 9 2005 3742
028 ABB0101 Feb 9 2005 3742
030 ABB0101 Feb 9 2005 51
030 ABB0101 Feb 9 2005 102
030 ABB0101 Feb 9 2005 102
031 ABB0101 Feb 9 2005 323
031 ABB0101 Feb 9 2005 646
031 ABB0101 Feb 9 2005 646
041 ABB0101 Feb 9 2005 22
041 ABB0101 Feb 9 2005 44
041 ABB0101 Feb 9 2005 44
042 ABB0101 Feb 9 2005 2
042 ABB0101 Feb 9 2005 4
042 ABB0101 Feb 9 2005 4

I want to remove the 2 rows with the highest values in 'NumberMailed' for each LetterCode and end up with the following:

LetterCode CycleName Mail Date NumberMailed
028 ABB0101 Feb 9 2005 1871
030 ABB0101 Feb 9 2005 51
031 ABB0101 Feb 9 2005 323
041 ABB0101 Feb 9 2005 22
042 ABB0101 Feb 9 2005 2

I know I should be using subqueries / nested queries, but have not yet come up with a working solution!

Hearty head pats

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-14 : 07:53:10

Since you're sure there are 3 of each, it might be easiest to delete all but the smallest...

SELECT LetterCode, CycleName, [Mail Date], MIN(NumberMailed) As MinNumberMailed
  FROM MyTable
  GROUP BY LetterCode, CycleName, [Mail Date]

the delete is easy once you know the rows not to delete

DELETE M
  FROM MyTable M
  LEFT OUTER JOIN (above query here
  ) X  ON M.LetterCode = X.LetterCode
       AND M.CycleName = X.CycleName
       AND M.[Mail Date] = X.[Mail Date]
       AND M.NumberMailed = X.MinNumberMailed
  WHERE X.NumberMailed IS NULL

Caveat emptor. I haven't tried this.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-14 : 07:54:00

Try


DELETE MyTable 
FROM MyTable t1 INNER JOIN (SELECT LetterCode, MAX(NumberMailed) AS NumberMailed
				FROM MyTable 
					GROUP BY LetterCode) t2
ON t1.LetterCode = t2.LetterCode AND t1.NumberMailed = t2.NumberMailed

I would suggest changing "DELETE MyTable" with a SELECT statement to ensure that you will get the desired results before deleting

Andy

Beauty is in the eyes of the beerholder

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-14 : 09:54:00

Thank you both! Got it working! I was on the right lines, but the syntax was not correct!!! Thanks again!

Hearty head pats

cas_o
Posting Yak Master

154 Posts

Posted - 2005-02-14 : 09:58:15

--delete
Select *
from
MyTable
where
exists
(
select null
from
MyTable t
where
t.LetterCode=MyTable.LetterCode
group by
t.LetterCode
having
MyTable.LetterCode > min(t.LetterCode)
)

Try the above, if the resultset is desired, comment out the select line with -- and remove -- from the delete line.

I always backup or copy entire data set to another table before these kind of operations.

;-]... Quack Waddle

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-14 : 11:53:15

Hi Cas

Thanks for the advice, I always like to obtain as many methods/approaches as possible. The only thing, when I try the select statement (as you advise, on a test table), the result set returned is empty?

I would like to pick this up tomorrow as I have to go home now (hooray), but any more advice in the meantime is very welcome!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources