Menu
I have simple question:
I have a postgresql table: 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.
875
Returning only the first N records in postgresql can be accomplished using limit keyword. Let's see how to get top 10 rows in postgresql and Get First N rows in postgresql.
PostgreSQL MAX() function is an aggregate function that returns the maximum value in a set of values. Syntax: MAX(expression); The MAX() function can be used with SELECT, WHERE and HAVING clause.
The PostgreSQL COUNT function counts a number of rows or non-NULL values against a specific column from a table. When an asterisk(*) is used with count function the total number of rows returns. Syntax: COUNT (* | [DISTINCT] ALL | column_name)
When a row is created, the value of xmin is set equal to the ID of the transaction that performed the INSERT command, while xmax is not filled in. When a row is deleted, the xmax value of the current version is labeled with the ID of the transaction that performed DELETE.
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
105
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
