CODEWITHSUNDEEP
sqlmax
DejaVuDBA
DejaVuDBA

Reputation: 19

Delete only non max records of a group

I am trying to do something like the following

delete PreOrder
where preorderid not in (
select preorderid
from PreOrder p1
inner join (select max(OrderDt) OrderDt, preorderid
from preorder group by preorderid) p2 on
p1.preorderid=p2.preorderid
where p1.preorder in (1,2,3,4,5)

I am just try to delete the record if there are dupes of just those. I got the max working, but how to delete just from those records that has that preorderid

Upvotes: 0

Views: 41

Answers (1)

Serif Emek
Serif Emek

Reputation: 674

Delete p1
from PreOrder p1
left join (select max(OrderDt) OrderDt, preorderid
from preorder group by preorderid) p2 on
p1.preorderid=p2.preorderid
and p1.OrderDt = p2.OrderDt
where p2.preorderid is null and p1.preorderid in (1,2,3,4,5)

Upvotes: 1

Related Questions