SQL SELECT TOP 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3453592/
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
Sql SELECT TOP 1
提问by David W
I'm getting weird results from a table I'm selecting from. Consider the following table:
我从我选择的表格中得到了奇怪的结果。考虑下表：
USERID TICKETSASSIGNED
1 0
100 0
101 0
102 0
103 0
Now, if I have the following sql as in:
现在，如果我有以下 sql：
SELECT TOP 1 USERID
FROM TABLEX
ORDER BY TICKETSASSIGNED
The result I would expect to get is "1" but most all the time I'm getting "100", the second record. Since TICKETSASSIGNEDvalues are all "0", it randomly picks which one it thinks is TOP 1 since this is the field I'm ordering on. To get the correct value of "1" in this case, I had to also order on USERID.
我希望得到的结果是“1”，但大多数时候我都得到“100”，即第二个记录。由于TICKETSASSIGNED值都是“0”，它随机选择它认为是 TOP 1 的一个，因为这是我订购的领域。在这种情况下，要获得正确的“1”值，我还必须在USERID.
Any ideas?
有任何想法吗？
回答by OMG Ponies
The result I would expect to get is "1" but most all the time I'm getting "100", the second record. Since TICKETSASSINGED values are all "0", it randomally picks which one it thinks is TOP 1 since this is the field I'm ordering on. To get the corredct value of "1" in this case, I had to also order on USERID.
我希望得到的结果是“1”，但大多数时候我都得到“100”，即第二个记录。由于 TICKETSASSINGED 值都是“0”，它会随机选择它认为是 TOP 1 的那个，因为这是我订购的领域。在这种情况下，要获得“1”的正确值，我还必须订购 USERID。
This is default behavior in all SQL - there's no order guarantee if there's no ORDER BY clause, and in this case you're not ordering by pertinent data so the database arbitrarily picks a row.
这是所有 SQL 中的默认行为 - 如果没有 ORDER BY 子句，则没有顺序保证，在这种情况下，您不是按相关数据排序，因此数据库会任意选择一行。
Use:
用：
ORDER BY TICKETSASSIGNED, USERID
回答by Lasse V. Karlsen
If you check the documentation for TOP: TOP (Transact-SQL), specifically the first two paragraphs under Remarks, it explicitly states that TOP does not order the rows. As such, the order you've imposed yourself is the one used.
如果您检查TOP: TOP (Transact-SQL)的文档，特别是备注下的前两段，它明确指出 TOP 不对行进行排序。因此，您强加给自己的顺序就是使用的顺序。
Unfortunately, your ordering is incomplete. It only happens to produce what you want sometimes.
很遗憾，您的订单不完整。它只是偶尔产生你想要的东西。
The correct way is exactly what you've figured out, you need to order by USERID as well.
正确的方法正是您所想到的，您还需要按 USERID 订购。
回答by Nick Craver
You would need a second column in your ORDER BYclause in this case:
ORDER BY在这种情况下，您的子句中需要第二列：
SELECT TOP 1 USERID
FROM TABLEX
ORDER BY TICKETSASSIGNED, USERID
Without this it's probablygiving you the records ordered by TICKETASSIGNED, then by their physical location in the table, e.g. the order they were inserted in most cases.
如果没有这个，它可能会给你排序的记录TICKETASSIGNED，然后是它们在表中的物理位置，例如在大多数情况下它们插入的顺序。
回答by rosscj2533
If you are ordering only by TICKETSASSIGNEDand they all have equal values, there is no reason any specific record should be returned as the top one. If you want to get the top one using USERIDas the next field to order by, then yes, you should just add USERIDto your order by clause.
如果您仅按以下方式排序TICKETSASSIGNED并且它们都具有相同的值，则没有理由将任何特定记录作为顶部记录返回。如果您想将顶部USERID用作下一个排序的字段，那么是的，您应该只添加USERID到您的 order by 子句中。
