Get first %p of column values

Question

Suppose we have the table:

Students (First_Name, Last_Name, Section_Name, Grades_Avg)

I need to write a query that gets the first %p (parameter of a procedure) of students for each section with the atributes (First_Name, Last_Name, Section_Name, Grades_Mean), descending order after Grades_Avg, ascending after Last_Name, with the Grades_Avg > 5.

My work:

select First_Name, Last_Name, Section_Name, Grades_Avg 
from STUDENTS where Grades_Avg>5 
ORDER BY Section_Name, Grades_Avg DESC 
FETCH FIRST p PERCENT ROWS ONLY;

But with this I don't get for each Section_Name (the first %p) i get overall for all sections.

Answer 1

Oracle has a function called percent_rank(), which might be able to help:

select s.*
from (select s.*,
             percent_rank() over (partition by section order by grades_avg) as p_rank
      from students s
      where s.grades_avg > 5
     ) s
where p_rank <= p;

This assumes that p is a number between 0 and 1. If it is between 0 and 100, then:

where p_rank <= p / 100

Note that students with the same grades have the same p_rank value, so they are with all included or all not included.

If you want an exact number returned -- for instance as close to 20% as possible even if all the students have the same grade, then row_number() and count() achieve that.