Aleksei Khatkevich
Aleksei Khatkevich

Reputation: 2197

Select top x% records

Question is regarding how to get top x% of records according to their ratings.

For example I have a table with a few columns, one of which is rating:

rating            smallint

value of rating is always positive.

My goal is to select top x% of entries according to their rating.

For example, for top 20%, if set of selected rows contains ratings like:

1,3,4,4,5,2,7,10,9

Then top 20% would be records with range from 8 to 10 → records with rating 9 and 10.

I implemented it in Django but it takes 2 calls to DB and I believe it can be easily achieved via SQL in PostgreSQL by just one call.

Any ideas how to implement it?

Upvotes: 1

Views: 60

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

Considering that the max rating available in the column is your base for max calculation.

Try this workaround:

select * from sample where rating >=(select max(rating)-max(rating)*20/100 from sample)

Demo on fiddle

Upvotes: 2

Related Questions