How to get the top 10 values in postgresql?

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

select *
from scores
order by score desc
fetch 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 scores
order by score desc
fetch first 10 rows only

SQL Fiddle

Seems you are looking for ORDER BY in DESCending order with LIMIT clause:

SELECT
 *
FROM
  scores
ORDER BY score DESC
LIMIT 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 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

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) subquery
where my_rank <= 10

(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)