Query in sql to get the top 10 percent in standard sql (without limit, top and the likes, without window functions)

Question

I'm wondering how to retrieve the top 10% athletes in terms of points, without using any clauses such as TOP, Limit etc, just a plain SQL query.

My idea so far:

Table Layout:

Score:

ID | Name | Points

Query:

select *
from Score s
where 0.10 * (select count(*) from Score x) >
     (select count(*) from Score p where p.Points < s.Points)

Is there an easier way to do this? Any suggestions?

Answer 1

Try:

select s1.id, s1.name s1.points, count(s2.points)
from score s1, score s2
where s2.points > s1.points
group by s1.id, s1.name s1.points
having count(s2.points) <= (select count(*)*.1 from score)

Basically calculates the count of players with a higher score than the current score, and if that count is less than or equal to 10% of the count of all scores, it's in the top 10%.