How to get the top 10 values in postgresql?

sql postgresql sql-limit

Joey Franklin · Dec 2, 2012 · Viewed 454.2k times · Source

I have simple question:

I have a postgresql database: Scores(score integer).

How would I get the highest 10 scores the fastest?

UPDATE:

I will be doing this query multiple times and am aiming for the fastest solution.

Answer

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