SQL 如何获得 postgresql 中的前 10 个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13674031/
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
How to get the top 10 values in postgresql?
提问by Joey Franklin
I have simple question:
我有一个简单的问题：
I have a postgresqldatabase: Scores(score integer).
我有一个postgresql数据库：Scores(score integer).
How would I get the highest 10 scores the fastest?
我如何最快获得最高的 10 分？
UPDATE:
更新：
I will be doing this query multiple times and am aiming for the fastest solution.
我将多次执行此查询，并力求获得最快的解决方案。
回答by Olaf Dietsche
For this you can use limit
为此，您可以使用限制
select *
from scores
order by score desc
limit 10
If performance is important (when is it not ;-) look for an index on score.
如果性能很重要（什么时候不重要；-）寻找得分指数。
Starting with version 8.4, you can also use the standard (SQL:2008) fetch first
从 8.4 版开始，您还可以使用标准 ( SQL:2008)fetch first
select *
from scores
order by score desc
fetch first 10 rows only
As @Raphvanns pointed out, this will give you the first 10 rowsliterally. To remove duplicate values, you have to select distinctrows, e.g.
正如@Raphvanns 指出的那样，这会给你first 10 rows字面意义。要删除重复值，您必须选择distinct行，例如
select distinct *
from scores
order by score desc
fetch first 10 rows only
回答by Grzegorz Gierlik
回答by Raphvanns
Note that if there are ties in top 10 values, you will only get the top 10 rows, not the top 10 valueswith the answers provided.
Ex: if the top 5 values are 10, 11, 12, 13, 14, 15 but your data contains
10, 10, 11, 12, 13, 14, 15 you will only get 10, 10, 11, 12, 13, 14 as your top 5 with a LIMIT
请注意，如果前 10 个值之间存在联系，您将只获得前 10 行，而不是提供答案的前 10 个值。例如：如果前 5 个值是 10, 11, 12, 13, 14, 15 但你的数据包含 10, 10, 11, 12, 13, 14, 15 你只会得到 10, 10, 11, 12, 13, 14 作为您的前 5 名LIMIT
Here is a solution which will return more than 10 rows if there are ties but you will get all the rows where some_value_columnis technically in the top 10.
这是一个解决方案，如果有平局，它将返回超过 10 行，但您将获得some_value_column技术上位于前 10 名的所有行。
select
*
from
(select
*,
rank() (order by some_value_column desc) as my_rank
from
mytable) subquery
where my_rank <= 10
回答by kashif
(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC
LIMIT 10)
UNION ALL
(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC
LIMIT 10)
