SQL 选择按 X 排序的前 N 个记录，但结果顺序相反
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2572496/
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
Select Top N Records Ordered by X, But Have Results in Reverse Order
提问by I. J. Kennedy
I'm trying to get the top N records (when ordered by some column X), but have the result set in reverse order. The following statement is incorrect, but probably demonstrates what I'm after:
我正在尝试获取前 N 条记录（按某些列 X 排序时），但结果集的顺序相反。以下陈述是不正确的，但可能表明了我的追求：
SELECT * FROM (SELECT TOP 10 * FROM FooTable ORDER BY X DESC) ORDER BY X ASC
For example, column X could be an ID or a timestamp; I want the latest 10 records but want them returned in forward chronological order.
例如，X 列可以是 ID 或时间戳；我想要最新的 10 条记录，但希望它们按时间顺序返回。
回答by davek
SELECT * FROM
(SELECT TOP 10 * FROM FooTable ORDER BY X DESC) as myAlias
ORDER BY X ASC
i.e. you might need an alias on your subquery, but other than that it should work.
即您的子查询可能需要一个别名，但除此之外它应该可以工作。
回答by SQLMenace
Try
尝试
SELECT * FROM
(SELECT TOP 10 * FROM FooTable ORDER BY X DESC) temp --alias
ORDER BY X
or with a common table expression (CTE)
或使用公用表表达式 (CTE)
WITH Temp AS (SELECT TOP 10 * FROM FooTable ORDER BY X DESC)
SELECT * FROM temp
ORDER BY X
回答by The Machine
ORDER BY clause is used to order the RESULT SET by a specified column.
ORDER BY 子句用于按指定列对 RESULT SET 进行排序。
Your query
Select TOP 10 * from FooTable ORDER BY X DESCassuming X is the timestamp ,is not going to return the most recently inserted 10 rows. It will return the top 10 rows as stored (in whichever order) in the Database, and will then return the result set of the 10 such rows , in descending order.
Hence your subquery should be modified to
您的查询
Select TOP 10 * from FooTable ORDER BY X DESC假设 X 是时间戳，不会返回最近插入的 10 行。它将返回存储在数据库中的前 10 行（以任何顺序），然后以降序返回 10 行的结果集。因此你的子查询应该修改为
Select TOP 10 * from (Select * from FooTable ORDER BY DESC) as T
Select TOP 10 * from (Select * from FooTable ORDER BY DESC) as T
This should fulfill your first requirement. You may then use this result set as an alias, to decide your final sort order.
这应该满足您的第一个要求。然后，您可以将此结果集用作别名，以决定您的最终排序顺序。
I hope i have understood you correctly, when you say "I'm trying to get the top N records (when ordered by some column X)"
我希望我能正确理解您，当您说“我正在尝试获取前 N 条记录（按某列 X 排序时）”
回答by Sunny
An alternate solution to this question for all non-supported versions for TOPkeyword is to use LIMIT. Example :-
对于TOP关键字的所有不受支持的版本，此问题的另一种解决方案是使用LIMIT。例子 ：-
SELECT * FROM
(SELECT * FROM FooTable ORDER BY X DESC LIMIT 10) as myAlias
ORDER BY X ASC
