删除最老的订单
方法一，首先查找最老的订单号码，然后进行删除，2步走，容易产生锁问题
Queue processing – Destructive read
? "retrieve and delete next in line"
?Retrieve "oldest" row in queue
SELECT ordernum, ... INTO :ordernum
FROM orders ORDER BY ordernum FETCH FIRST ROW ONLY; 1I/O, S
?Delete the row
DELETE FROM order WHERE :ordernum = ordernum; 1 I/O, S->X
? Deadlock, 2 SQL Stmts, single row (or IN list)

方法二，改进了一些，不容易产生锁问题
? Delete through ORDER BY
SELECT ordernum, ... INTO :ordernum, ... FROM OLD TABLE(DELETE FROM (SELECT * FROM orders ORDER BY ordernum FETCH FIRST ROW ONLY));

把一个表中的一些行move到另外一个表中，一个SQL实现
CREATE TABLE Archive LIKE Inventory;

WITH del (
    Item
    ,Quantity
    ,InvDate
    )
AS (
    SELECT Item
        ,Quantity
        ,InvDate
    FROM OLD TABLE (
            DELETE
            FROM (
                SELECT Item
                    ,Quantity
                    ,InvDate
                    ,row_number() OVER (
                        PARTITION BY Item ORDER BY InvDate DESC
                        ) AS rn
                FROM Inventory
                )
            WHERE rn > 1
            )
    )
    ,ins (x)
AS (
    SELECT 1
    FROM NEW TABLE (
            INSERT INTO Archive
            SELECT *
            FROM del
            )
    )
SELECT COUNT(1)
FROM ins;