How to get the top 10 values in postgresql

Question

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

User · Answer

SELECT  lt some columns gt  FROM mytable  lt maybe some joins here gt  WHERE  lt various conditions gt  ORDER BY date DESC LIMIT 10   UNION ALL   SELECT  lt some columns gt  FROM mytable  lt maybe some joins here gt  WHERE  lt various conditions gt  ORDER BY date ASC     LIMIT 10

User · 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   SQL Fiddle

User · Answer

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  lt   10

User · Answer

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

[sql] How to get the top 10 values in postgresql?

Examples related to sql

Examples related to postgresql

Examples related to sql-limit