Monday, 26 August 2013

Delete all rows except (my query)

Delete all rows except (my query)

I've spent a bit of time looking for this situation and found many that
are close but I can't get it to work with my situation.
To simplify, I have TABLE t with contact_id and Code
contact_id Code
__________ ____
123456 100
123456 200
123456 300
987654 100
987654 200
654321 300
For each contact_id I want to select the row with the highest code and
delete the others. I did come up with a select statement to show what I
want the table to look like:
select distinct contact_id, MAX(code) OVER(Partition by contact_id) AS code
from t
Based on above then my result set is:
123456 300
987654 200
654321 300
How can I delete all rows except the ones produced by this select
statement. I tried using a NOT IN clause but this of course will not
support multiple columns.

No comments:

Post a Comment