按自然顺序选择底行 [英] select bottom rows in natural order

问题描述

如何根据自然顺序选择表格的底部 X 行?我不能做按 DESC 排序..."，因为我没有按任何列号排序...

How can I select the bottom X rows of a table, based on natural order? I can't do "ORDER BY DESC...", since I'm not ordering it by any column number...

我使用的是 Sql Server 2008 R2.

I'm using Sql Server 2008 R2.

推荐答案

SQL 不保证表中行的顺序.它仅通过显式的 ORDER BY 来保证查询中的行顺序.依赖聚集索引也是不明智的.一个聚集索引可能会因为好的原因、坏的原因或根本没有原因而被更改或删除.此外，查询优化器不能保证以与聚集索引相同的顺序返回行.在没有明确的 ORDER BY 的情况下，不能保证从一次运行到下一次以相同的顺序返回行.(优化器可以在它认为应该的时候做出不同的决定.)其中任何一件事情都可能破坏您的代码.

SQL doesn't guarantee the order of rows in a table. It only guarantees the order of rows in a query with an explicit ORDER BY. It's not wise to rely on a clustered index, either. A clustered index might be changed or dropped for good reasons, bad reasons, or no reason at all. Also, the query optimizer isn't guaranteed to return rows in the same order as a clustered index. In the absence of an explicit ORDER BY, it's not guaranteed to return rows in the same order from one run to the next. (The optimizer can make different decisions whenever it thinks it should.) Any one of those things can break your code.

相反，请使用查询.按时间戳列降序排序.(ORDER BY mytimestampcolumn DESC) 您可以将顶部的n"行剪掉.由于您按降序排序，因此顶行是底行.(无法抗拒.)

Instead, use a query. Sort descending on a timestamp column. (ORDER BY mytimestampcolumn DESC) You can nip the top 'n' rows off that. Since you sort descending, the top rows are the bottom rows. (Couldn't resist.)

如果时间戳列失败，您可以尝试使用自动递增的 ID 编号列，尽管它们不能保证严格按时间顺序排列.(ID 编号为 1000 的事务可能在编号为 999 和 998 的事务之前提交.)

Failing a timestamp column, you might try the same with an auto-incrementing id number column, although they're not guaranteed to be in strictly chronological order. (The transaction that gets id number 1000 might commit before the transactions that got numbers 999 and 998.)

这篇关于按自然顺序选择底行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！