Sybase tricky sql -- How to delete the earliest 2 records?

Table:

 

create table BE_TES_FEED (

    TRADE_STATUS VARCHAR(1) NOT NULL

    ,INSERT_DATE DATETIME NOT NULL

) lock datarows

go

 

Data:

 

INSERT INTO BE_TES_FEED VALUES ('1', getDate())

INSERT INTO BE_TES_FEED VALUES ('2', getDate())

INSERT INTO BE_TES_FEED VALUES ('3', getDate())

INSERT INTO BE_TES_FEED VALUES ('4', getDate())

INSERT INTO BE_TES_FEED VALUES ('5', getDate())

INSERT INTO BE_TES_FEED VALUES ('6', getDate())

INSERT INTO BE_TES_FEED VALUES ('7', getDate())

INSERT INTO BE_TES_FEED VALUES ('8', getDate())

INSERT INTO BE_TES_FEED VALUES ('9', getDate())

INSERT INTO BE_TES_FEED VALUES ('10', getDate())

 

sql:

 

DELETE FROM BE_TES_FEED

WHERE INSERT_DATE IN

(

SELECT INSERT_DATE

FROM BE_TES_FEED T2 

WHERE 2 > (SELECT COUNT(*) FROM BE_TES_FEED  T3 WHERE T3.INSERT_DATE < T2.INSERT_DATE)

)

 

The core idea:

 

Pay attention to the yellow part, and the data.

 

There is 0 record earlier than the 1 record.

There is 1 record earlier than the 2 record.

There are 2 records earlier than the 3 record.

… …

 

Show me all those records, with this number less than 2, and delete them!!

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值