SQL 如何选择最底部的行？

How to select bottom most rows?

提问by MetaGuru

I can do SELECT TOP (200) ... but why not BOTTOM (200)?

我可以做 SELECT TOP (200) ...但为什么不做 BOTTOM (200)？

Well not to get into philosophy what I mean is, how can I do the equivalent of TOP (200) but in reverse (from the bottom, like you'd expect BOTTOM to do...)?

好吧，不要进入哲学，我的意思是，我怎么能做相当于 TOP (200) 但反过来（从底部开始，就像你期望 BOTTOM 做的那样......）？

采纳答案by Tom H

SELECT
    columns
FROM
(
     SELECT TOP 200
          columns
     FROM
          My_Table
     ORDER BY
          a_column DESC
) SQ
ORDER BY
     a_column ASC

回答by Justin Ethier

It is unnecessary. You can use an ORDER BYand just change the sort to DESCto get the same effect.

这是不必要的。您可以使用 anORDER BY并将排序更改为DESC以获得相同的效果。

回答by Martijn Burger

Sorry, but I don't think I see any correct answers in my opinion.

对不起，但我认为我没有看到任何正确的答案。

The TOPx function shows the records in undefined order. From that definition follows that a BOTTOMfunction can not be defined.

该TOPX功能显示在未确定的订单记录。根据该定义，BOTTOM无法定义函数。

Independent of any index or sort order. When you do an ORDER BY y DESCyou get the rows with the highest y value first. If this is an autogenerated ID, it should show the records last added to the table, as suggested in the other answers. However:

独立于任何索引或排序顺序。当您执行 a 时，ORDER BY y DESC您首先获得具有最高 y 值的行。如果这是一个自动生成的 ID，它应该显示上次添加到表中的记录，如其他答案中所建议的。然而：

• This only works if there is an autogenerated id column
• It has a significant performance impact if you compare that with the TOPfunction

• 这仅在有自动生成的 id 列时才有效
• 如果将其与TOP函数进行比较，它会对性能产生重大影响

The correct answer should be that there is not, and cannot be, an equivalent to TOPfor getting the bottom rows.

正确的答案应该是没有，也不可能有等价TOP于获取底行的方法。

回答by Shadi Namrouti

Select Bottom 1000 from Employee

从员工中选择后 1000 名

DECLARE 
@bottom int,
@count int

SET @bottom = 1000 
SET @count = (select COUNT(*) from Employee)

select * from Employee emp where emp.EmployeeID not in 
(
SELECT TOP (@count-@bottom) Employee.EmployeeID FROM Employee
)

回答by user9323238

It would seem that any of the answers which implement an ORDER BY clause in the solution is missing the point, or does not actually understand what TOP returns to you.

似乎在解决方案中实现 ORDER BY 子句的任何答案都没有抓住要点，或者实际上并不理解 TOP 返回给您的内容。

TOP returns an unordered query result set which limits the record set to the first N records returned. (From an Oracle perspective, it is akin to adding a where ROWNUM < (N+1).

TOP 返回一个无序查询结果集，该结果集将记录集限制为返回的前 N ​​条记录。（从 Oracle 的角度来看，它类似于添加一个 where ROWNUM < (N+1)。

Any solution which uses an order, mayreturn rows which also are returned by the TOP clause (since that data set was unordered in the first place), depending on what criteria was used in the order by

任何使用顺序的解决方案都可能返回也由 TOP 子句返回的行（因为该数据集首先是无序的），具体取决于顺序中使用的条件

The usefulness of TOP is that once the dataset reaches a certain size N, it stops fetching rows. You can get a feel for what the data looks like without having to fetch all of it.

TOP 的用处在于，一旦数据集达到特定大小 N，它就会停止获取行。您无需获取所有数据即可了解数据的外观。

To implement BOTTOM accurately, it would need to fetch the entire dataset unordered and then restrict the dataset to the final N records. That will not be particularly effective if you are dealing with huge tables. Nor will it necessarily give you what you thinkyou are asking for. The end of the data set may not necessarily be "the last rows inserted" (and probably won't be for most DML intensive applications).

为了准确地实现 BOTTOM，它需要无序地获取整个数据集，然后将数据集限制为最后的 N 条记录。如果您正在处理大表，这将不会特别有效。它也不一定会给你你认为你要求的东西。数据集的结尾可能不一定是“最后插入的行”（对于大多数 DML 密集型应用程序可能不会）。

Similarly, the solutions which implement an ORDER BY are, unfortunately, potentially disastrous when dealing with large data sets. If I have, say, 10 Billion records and want the last 10, it is quite foolish to order 10 Billion records and select the last 10.

同样，不幸的是，实现 ORDER BY 的解决方案在处理大型数据集时可能是灾难性的。如果我有 100 亿条记录并且想要最后 10 条记录，那么订购 100 亿条记录并选择最后 10 条记录是非常愚蠢的。

The problem here, is that BOTTOM does not have the meaning that we think ofwhen comparing it to TOP.

这里的问题是，当与 TOP 比较时，BOTTOM 没有我们想到的含义。

When records are inserted, deleted, inserted, deleted over and over and over again, some gaps will appear in the storage and later, rows will be slotted in, if possible. But what we often see, when we select TOP, appearsto be sorted data, because it may have been inserted early on in the table's existence. If the table does not experience many deletions, it may appearto be ordered. (e.g. creation dates may be as far back in time as the table creation itself). But the reality is, if this is a delete-heavy table, the TOP N rows may not look like that at all.

当记录被一遍遍地插入、删除、插入、删除时，存储中会出现一些间隙，然后，如果可能的话，将插入行。但是我们经常看到，当我们选择 TOP 时，似乎是排序的数据，因为它可能在表存在的早期就已经插入了。如果表没有经历很多删除，它可能看起来是有序的。（例如，创建日期可能与表创建本身的时间一样早）。但实际情况是，如果这是一个需要大量删除的表，则 TOP N 行可能根本不是这样。

So -- the bottom line here(pun intended) is that someone who is asking for the BOTTOM N records doesn't actually know what they're asking for. Or, at least, what they're asking for and what BOTTOM actually means are not the same thing.

所以——这里的底线（双关语）是要求 BOTTOM N 记录的人实际上并不知道他们在要求什么。或者，至少，他们的要求和 BOTTOM 的实际含义不是一回事。

So -- the solution may meet the actual business need of the requestor...but does not meet the criteria for being the BOTTOM.

所以——该解决方案可能满足请求者的实际业务需求……但不符合作为底层的标准。

回答by tomosius

The currently accepted answer by "Justin Ethier" is not a correct answer as pointed out by "Protector one".

“Justin Ethier”目前接受的答案并不是“保护者一号”所指出的正确答案。

As far as I can see, as of now, no other answer or comment provides the equivalent of BOTTOM(x) the question author asked for.

据我所知，截至目前，没有其他答案或评论提供与作者要求的 BOTTOM(x) 等效的问题。

First, let's consider a scenario where this functionality would be needed:

首先，让我们考虑需要此功能的场景：

SELECT * FROM Split('apple,orange,banana,apple,lime',',')

This returns a table of one column and five records:

这将返回一个包含一列和五条记录的表：

• apple
• orange
• banana
• apple
• lime

• 苹果
• 橘子
• 香蕉
• 苹果
• 酸橙

As you can see: we don't have an ID column; we can't order by the returned column; and we can't select the bottom two records using standard SQL like we can do for the top two records.

如您所见：我们没有 ID 列；我们不能按返回的列排序；并且我们不能像选择前两条记录那样使用标准 SQL 选择后两条记录。

Here is my attempt to provide a solution:

这是我试图提供解决方案的尝试：

SELECT * INTO #mytemptable FROM Split('apple,orange,banana,apple,lime',',')
ALTER TABLE #mytemptable ADD tempID INT IDENTITY
SELECT TOP 2 * FROM #mytemptable ORDER BY tempID DESC
DROP TABLE #mytemptable

And here is a more complete solution:

这是一个更完整的解决方案：

SELECT * INTO #mytemptable FROM Split('apple,orange,banana,apple,lime',',')
ALTER TABLE #mytemptable ADD tempID INT IDENTITY
DELETE FROM #mytemptable WHERE tempID <= ((SELECT COUNT(*) FROM #mytemptable) - 2)
ALTER TABLE #mytemptable DROP COLUMN tempID
SELECT * FROM #mytemptable
DROP TABLE #mytemptable

I am by no means claiming that this is a good idea to use in all circumstances, but it provides the desired results.

我绝不声称这是在所有情况下都可以使用的好主意，但它提供了预期的结果。

回答by Paul

The problem with ordering the other way is that it often does not make good use of indices. It is also not very extendable if you ever need to select a number of rows that are not at the start or the end. An alternative way is as follows.

以另一种方式排序的问题在于它通常不能很好地利用索引。如果您需要选择不在开头或结尾的许多行，它也不是很可扩展。另一种方法如下。

DECLARE @NumberOfRows int;
SET @NumberOfRows = (SELECT COUNT(*) FROM TheTable);

SELECT col1, col2,...
FROM (
    SELECT col1, col2,..., ROW_NUMBER() OVER (ORDER BY col1) AS intRow
    FROM TheTable
) AS T
WHERE intRow > @NumberOfRows - 20;

回答by Justin Swartsel

All you need to do is reverse your ORDER BY. Add or remove DESCto it.

您需要做的就是反转您的ORDER BY. 添加或删除DESC它。

回答by user3598017

"Tom H" answer above is correct and it works for me in getting Bottom 5 rows.

上面的“Tom H”答案是正确的，它适用于我获得底部 5 行。

SELECT [KeyCol1], [KeyCol2], [Col3]
FROM
(SELECT TOP 5 [KeyCol1],
       [KeyCol2],
       [Col3]
  FROM [dbo].[table_name]
  ORDER BY [KeyCol1],[KeyCol2] DESC) SOME_ALAIS
  ORDER BY [KeyCol1],[KeyCol2] ASC

Thanks.

谢谢。

回答by sheppe

Querying a simple subquery sorted descending, followed by sorting on the same column ascending does the trick.

查询一个按降序排序的简单子查询，然后在同一列上按升序排序就行了。

SELECT * FROM 
    (SELECT TOP 200 * FROM [table] t2 ORDER BY t2.[column] DESC) t1
    ORDER BY t1.[column]