How to display top 5 values in Postgresql?-CodePudding

I have a database query that displays students and their average rating in descending order. Here is the query snippet:

enter image description here

And I only want to get the 5 best performing students. Moreover, it is desirable in the least expensive way, because this request will be performed repeatedly

CodePudding user response：

You can use LIMIT to set how many rows you want back.

SELECT name, AVG(score) as average_rating from students
GROUP BY name
ORDER BY average_rating DESC
LIMIT 5;

CodePudding user response：

If you are using PostgreSql 13 then use FETCH FIRST

SELECT name, 
           AVG(score) as avg_score
FROM students
GROUP BY name
ORDER BY avg_score DESC
FETCH FIRST 5 ROWS WITH TIES

If you have 6 names with the same maximum average score then WITH TIES will return all of them.