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

SQL Fiddle

Seems you are looking for ORDER BY in DESCending 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