Here I have a table called Scores:

What I want to do is to select student_id, class_id, course, score and rank based on the students' scores. However, we need to separate students into different groups before ranking, which means those students who have the same class_id and course can be ranked with each other. Plus, we need to sort the results by class_id, course, score and student_id in descending order. (You could not use rank() to do the ranking, you could only use subquery)

The results should be like:

My attempt: I could select class_id, course, score and student_id from the table Scores in descending order and rank all the students, but I have no idea how to rank the students in the same class_id and course

CodePudding user response：

create table Scores (
  student_id int,
  class_id int,
  course int,
  score int
);

insert into Scores (student_id, class_id, course, score)
values ( 1, 1, 1, 80);
insert into Scores (student_id, class_id, course, score)
values ( 2, 1, 1, 80);
insert into Scores (student_id, class_id, course, score)
values ( 3, 2, 3, 75);
insert into Scores (student_id, class_id, course, score)
values ( 4, 3, 2, 90);
insert into Scores (student_id, class_id, course, score)
values ( 5, 1, 2, 85);
insert into Scores (student_id, class_id, course, score)
values ( 6, 2, 3, 85);
insert into Scores (student_id, class_id, course, score)
values ( 7, 2, 3, 85);
insert into Scores (student_id, class_id, course, score)
values ( 8, 3, 4, 78);
insert into Scores (student_id, class_id, course, score)
values ( 9, 3, 4, 76);
insert into Scores (student_id, class_id, course, score)
values (10, 3, 4, 79);

select student_id
  , class_id
  , course
  , score
  , RANK() OVER (
    PARTITION BY class_id, course
    ORDER BY score desc
  ) as "rank"
from Scores
order by class_id desc
  , course desc
  , score desc
  , student_id desc

CodePudding user response：

Are you really looking for such a simple query

select student_id, class_id, course, score, rank
from scores
order by student_id desc, class_id desc, course desc, score asc

each field in order by can have be ascending or descending.