SQL DELETE all rows apart from last N rows for each unique value
Here's a tough one, How would I delete all but the last, say 3 rows, for each un开发者_Go百科ique value in a different field?
Here's a visual of the problem:
id | otherfield
---------------
1 | apple <- DELETE
2 | banana <- KEEP
3 | apple <- DELETE
4 | apple <- KEEP
5 | carrot <- KEEP
6 | apple <- KEEP
7 | apple <- KEEP
8 | banana <- KEEP
How would I accomplish this in SQL?
Non tested, but something along these lines might work:
DELETE t.*
FROM table t JOIN (
SELECT id
@rowNum := IF(@otherfield <> otherfield, 1, @rowNum + 1) rn,
@otherfield := otherfield otherfield
FROM (
SELECT id, otherfield
FROM table
ORDER BY otherfield, id DESC
) t, (SELECT @otherfield := NULL, @rowNum := -1) dm
) rs ON t.id = rs.id
WHERE rs.rn > 3
Delete MyTable
Where Id In (
Select Id
From (
Select Id
, (Select COUNT(*)
From MyTable As T2
Where T2.OtherField = T.OtherField
And T2.Id > T.Id) As Rnk
From MyTable As T
) As Z
Where Z.Rnk > 2
)
Another version which might be a bit faster:
Delete MyTable
Where Id In (
Select T.Id
From MyTable As T
Left Join MyTable As T2
On T2.OtherField = T.OtherField
And T2.Id > T.Id
Group By T.Id
Having Count(T2.Id) > 2
)
精彩评论