How to get the top 10 values in postgresql?
For this you can use limit
select *from scoresorder by score desclimit 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
select *from scoresorder by score descfetch first 10 rows only
As @Raphvanns pointed out, this will give you the first 10 rows
literally. To remove duplicate values, you have to select distinct
rows, e.g.
select distinct *from scoresorder by score descfetch first 10 rows only
Seems you are looking for ORDER BY
in DESC
ending order with LIMIT clause:
SELECT *FROM scoresORDER BY score DESCLIMIT 10
Of course SELECT *
could seriously affect performance, so use it with caution.
Note that if there are ties in top 10 values, you will only get the top 10 rows, not the top 10 values with the answers provided.Ex: if the top 5 values are 10, 11, 12, 13, 14, 15 but your data contains10, 10, 11, 12, 13, 14, 15 you will only get 10, 10, 11, 12, 13, 14 as your top 5 with a 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_column
is technically in the top 10.
select *from (select *, rank() over (order by some_value_column desc) as my_rank from mytable) subquerywhere my_rank <= 10