oracle 使用 SQL 删除前 1 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28896632/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete top 1 row using SQL
提问by anjali
I have a SQL, the purpose is to keep the last two entries and delete the top rows .
我有一个 SQL，目的是保留最后两个条目并删除顶部行。
delete from table
where RowID in (select top 10 RowID from table)
This deletes all rows instead of the first rows I intend to delete.
这将删除所有行而不是我打算删除的第一行。
In the interface I am using, 'WITH' command does not work. It has to be something like the above query.
在我使用的界面中，“WITH”命令不起作用。它必须类似于上面的查询。
I have a table with 3 columns x, y, z. I can't rely on the pseudo column rownum as when I delete some rows the rownum don't change. As this delete query will be running every 60 sec and the rownum of the table won't start from 1 every time.
我有一个 3 列的表x, y, z。我不能依赖伪列 rownum，因为当我删除一些行时，rownum 不会改变。由于此删除查询将每 60 秒运行一次，并且表的 rownum 不会每次都从 1 开始。
I want to delete all other rows except the last two entries. Topwill work
我想删除除最后两个条目之外的所有其他行。Top将工作
delete from custom.colperformance
where RowID in (select top 2 RowID
from custom.colperformance
order by RowID desc)
This is giving me an error
这给了我一个错误
Table structure
ProfileTime TriggerTime RowId
12 3 4
12 5 6
6 7 2
here Rowid comes in random if we delete some rows in between
如果我们删除中间的一些行，这里的 Rowid 是随机的
Please help!! .. thanks in advance
请帮忙！！.. 提前致谢
回答by Atilla Ozgur
If this is oracle you can not use TOP 10, use following syntax:
如果这是 oracle，您不能使用 TOP 10，请使用以下语法：
delete from table where RowID in (select RowID from table where rownum <= 10)
Of course you should also give order by
当然你也应该下令
delete from table where RowID in (select RowID from table where rownum <= 10 ORDER BY table.columnX)
回答by DarekK
DELETE FROM table_name LIMIT 1
回答by Barrie van Boven
first select the top 1 from the entire table, then select the top one from the entire table where you leave out the result of the first query
首先从整个表中选择顶部的 1，然后从整个表中选择顶部的一个，其中您遗漏了第一个查询的结果
select top 1 (RowID)
from table
where RowID NOT IN (select top 1 RowID from table)
now you know which rows you do not want to delete. save in temp table maybe?
现在您知道不想删除哪些行了。保存在临时表中吗？
