CODEWITHSUNDEEP
sqlpostgresql
exemee
exemee

Reputation: 11

How to display top 5 values in Postgresql?

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

Upvotes: 1

Views: 6525

Answers (2)

Radim Bača
Radim Bača

Reputation: 10711

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.

Upvotes: 1

Tomáš Šturm
Tomáš Šturm

Reputation: 519

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;

Upvotes: 0

Related Questions