sql - 删除除最近的 1 行以外的所有行

Question

想要从表中删除除最新的 1 行之外的所有行。

此查询适用于 SQL Server 2008 标准版:

DELETE S1 
FROM StateLogs S1, StateLogs S2 
WHERE S1.NodeId = S2.NodeId AND S1.NodeId = {0} AND S1.Modified < S2.Modified

但在 SQL Server Compact Edition 3.5 上失败

There was an error parsing the query.
[ Token line number = 1, Token line offset = 11,Token in error = FROM ]

精简版的等效项是什么？

编辑

表格设计http://pastebin.com/Akwpypkm

Answer 1

您的删除语句似乎有语法错误，请尝试将查询更改为

DELETE S1 FROM ...

到

DELETE FROM ...

更新:

试试这个语句来代替你的:

DELETE FROM StateLogs WHERE Id NOT IN (SELECT TOP (1) Id FROM StateLogs ORDER BY Modified DESC)

另外请注意，根据您提供的模式对表进行插入/更新操作可能会遇到问题，因为您的主键大于最大值:http://msdn.microsoft.com/en-us/library/ms191241(v=sql.105).aspx

这可能会导致您的应用程序出现神秘的运行时故障。