Access SQL：按条件删除重复标识的指定记录

Got it, let's work through this problem properly. Your goal is to keep the record with the highest Cost for each duplicate OrderNum, and safely remove the rest—including the tricky edge case where multiple records share the same maximum Cost for the same OrderNum.

Core Problem with Your Current Approach #

If you only filter by Cost, you’ll hit issues when two records have the same Cost for the same OrderNum—your logic won’t know which one to keep, and you might accidentally delete all of them or leave duplicates behind. The fix is to add a tiebreaker (like a unique ID, row identifier, or even just an arbitrary sort) to ensure we only keep one record per OrderNum.

Solutions by Database Type #

Below are tested solutions for common databases, all handling the same-Cost edge case:

1. MySQL (8.0+ with Window Functions)

This is the cleanest approach if your MySQL version supports window functions. We’ll assign a rank to each record in an OrderNum group, sorting first by Cost descending, then by a unique ID (like ID) to break ties:

-- First, verify which records will be deleted (always do this first!)
WITH RankedOrders AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY OrderNum 
               ORDER BY Cost DESC, ID ASC -- ID as tiebreaker; use any unique column
           ) AS rn
    FROM Orders
)
SELECT * FROM RankedOrders WHERE rn > 1;

-- If the verification looks good, run the delete
WITH RankedOrders AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY OrderNum 
               ORDER BY Cost DESC, ID ASC
           ) AS rn
    FROM Orders
)
DELETE FROM Orders
WHERE ID IN (SELECT ID FROM RankedOrders WHERE rn > 1);

2. MySQL (Pre-8.0, No Window Functions)

If you’re on an older MySQL version, use a JOIN approach with subqueries to find the records to keep:

-- Verify first
SELECT o1.*
FROM Orders o1
LEFT JOIN (
    SELECT OrderNum, MAX(Cost) AS MaxCost, MIN(ID) AS KeepID
    FROM Orders
    GROUP BY OrderNum
) o2 ON o1.OrderNum = o2.OrderNum
WHERE o1.Cost < o2.MaxCost OR (o1.Cost = o2.MaxCost AND o1.ID != o2.KeepID);

-- Delete
DELETE o1
FROM Orders o1
LEFT JOIN (
    SELECT OrderNum, MAX(Cost) AS MaxCost, MIN(ID) AS KeepID
    FROM Orders
    GROUP BY OrderNum
) o2 ON o1.OrderNum = o2.OrderNum
WHERE o1.Cost < o2.MaxCost OR (o1.Cost = o2.MaxCost AND o1.ID != o2.KeepID);

3. SQL Server / Azure SQL

Use a CTE with ROW_NUMBER() to rank records, then delete the non-top-ranked ones:

-- Verify
WITH RankedOrders AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY OrderNum 
               ORDER BY Cost DESC, OrderID ASC -- Replace OrderID with your unique key
           ) AS rn
    FROM Orders
)
SELECT * FROM RankedOrders WHERE rn > 1;

-- Delete
WITH RankedOrders AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY OrderNum 
               ORDER BY Cost DESC, OrderID ASC
           ) AS rn
    FROM Orders
)
DELETE FROM RankedOrders WHERE rn > 1;

4. PostgreSQL

PostgreSQL has a handy implicit row identifier ctid you can use as a tiebreaker if you don’t have an explicit unique key:

-- Verify
SELECT *
FROM Orders o
WHERE EXISTS (
    SELECT 1
    FROM Orders o2
    WHERE o2.OrderNum = o.OrderNum
    AND (o2.Cost > o.Cost OR (o2.Cost = o.Cost AND o2.ctid < o.ctid))
);

-- Delete
DELETE FROM Orders o
WHERE EXISTS (
    SELECT 1
    FROM Orders o2
    WHERE o2.OrderNum = o.OrderNum
    AND (o2.Cost > o.Cost OR (o2.Cost = o.Cost AND o2.ctid < o.ctid))
);

Critical Notes #

• Always back up your data before running delete operations!
• Verify first: Run the SELECT version of the query to confirm you’re only targeting the records you want to delete.
• If your table doesn’t have a unique ID, use any column that can help distinguish records (like creation timestamp, if you have one) as the tiebreaker.

内容的提问来源于stack exchange，提问作者Solid Snake