SQL MAX vs Top 1 - 哪个更好？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7198274/
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
MAX vs Top 1 - which is better?
提问by Craig
I had to review some code, and came across something that someone did, and can't think of a reason why my way is better and it probably isn't, so, which is better/safer/more efficient?
我不得不一些代码，并遇到了某人做过的事情，并且想不出为什么我的方法更好的原因，它可能不是，那么，哪个更好/更安全/更有效？
SELECT MAX(a_date) FROM a_table WHERE a_primary_key = 5 GROUP BY event_id
OR
或者
SELECT TOP 1 a_date FROM a_table WHERE a_primary_key = 5 ORDER BY a_date
I would have gone with the 2nd option, but I'm not sure why, and if that's right.
我会选择第二个选项，但我不确定为什么，如果那是对的。
采纳答案by Chains
Performance is generally similar, if your table is indexed.
如果您的表已编入索引，则性能通常相似。
Worth considering though: Topusually only makes sense if you're ordering your results (otherwise, topof what?)
值得考虑的，但：Top通常才有意义，如果你订购你的结果（否则top的是什么？）
Ordering a result requires more processing.
对结果进行排序需要更多的处理。
Min doesn't always require ordering. (Just depends, but often you don't need order by or group by, etc.)
Min 并不总是需要订购。（只是取决于，但通常你不需要 order by 或 group by 等）
In your two examples, I'd expect speed / x-plan to be very similar. You can always turn to your stats to make sure, but I doubt the difference would be significant.
在您的两个示例中，我希望 speed / x-plan 非常相似。你可以随时查看你的统计数据来确定，但我怀疑差异会很大。
回答by Junior Mayhé
1) When there is a clustered indexon the table and the column to be queried, both the MAX()operator and the query SELECT TOP 1will have almost identical performance.
1）当表和要查询的列上有聚集索引时，MAX()操作符和查询的SELECT TOP 1性能几乎相同。
2) When there is no clustered indexon the table and the column to be queried, the MAX()operator offers the better performance.
2）当表和要查询的列上没有聚集索引时，MAX()操作符提供更好的性能。
Reference: http://www.johnsansom.com/performance-comparison-of-select-top-1-verses-max/
参考：http: //www.johnsansom.com/performance-comparison-of-select-top-1-verses-max/
回答by GSerg
They are different queries.
它们是不同的查询。
The first one returns many records (the biggest a_datefor each event_idfound within a_primary_key = 5)
第一个返回许多记录（在 中找到a_date的每个记录中最大的）event_ida_primary_key = 5
The second one returns one record (the smallest a_datefound within a_primary_key = 5).
第二个返回一个记录（在 中a_date找到的最小的a_primary_key = 5）。
回答by vol7ron
For the queries to have the same result you would need:
要使查询具有相同的结果，您需要：
SELECT MAX(a_date) FROM a_table WHERE a_primary_key = 5
SELECT TOP 1 a_date FROM a_table WHERE a_primary_key = 5 ORDER BY a_date DESC
The best way to know which is faster is to check the query plan and do your benchmarks. There are many factors that would affect the speed, such as table/heap size, etc. And even different versions of the same database may be optimized to favor one query over the other.
了解哪个更快的最好方法是检查查询计划并进行基准测试。有很多因素会影响速度，例如表/堆大小等。甚至可以优化同一数据库的不同版本以支持一个查询而不是另一个查询。
回答by Jay Magwadiya
I perform max and top on one table with 20,00,000+ records , and found that Top give faster resultwith order by than max or min function.
我在一张有 20,00,000 多条记录的表上执行 max 和 top ，发现Top通过 order by 比 max 或 min 函数给出更快的结果。
So , best way is to execute both your query one by one for some time and check connection elapsed time for than.
因此，最好的方法是在一段时间内一一执行您的查询并检查连接经过的时间。
回答by Nick Rolando
MAXand TOPfunction differently. Your first query will return the maximum value found for a_datethat has a a_primary_key = 5for each different event_idfound. The second query will simply grab the first a_datewith a a_primary_key = 5found in the result set.
MAX和TOP功能不同。您的第一个查询将返回找到的最大值a_date，a_primary_key = 5对于每个不同的event_id发现都有一个。第二个查询将简单地获取第一个a_date，a_primary_key = 5并在结果集中找到一个。
