How to group by and rank in sql , then select other columns from table all rows for a particular group should be together

user_14138477 0
Viewed 177

I want to group rows based on an id column and order by sum of an another column. Then I need these rows with all columns from table as ordered groups.

Here an example what I am trying to do:

SELECT Studentname, 
       Subject, 
       Marks, 
       RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) Rank
FROM ExamResult order by Studentname

Result:

Studentname Subject  Marks  Rank
Isabella    english  90     1
Isabella    Science  70     2
Isabella    Maths    50     3
Lily        Science  80     1
Lily        english  75     2
Lily        Maths    65     3
Olivia      english  95     1
Olivia      Science  60     2
Olivia      Maths    60     3

What i need is order by sum(marks) then group all records for that student as one entity. As here total marks for Isabella, Lily and Olivia are 210, 220 and 215. So order for group should be Lily, Olivia, and Isabella.

required result:

Studentname Subject  Marks  Rank
Lily        Science  80     1
Lily        english  75     1
Lily        Maths    65     1
Olivia      english  95     2
Olivia      Science  60     2
Olivia      Maths    60     2
Isabella    english  90     3
Isabella    Science  70     3
Isabella    Maths    50     3
rank group-by sql-server sql
1 Answers

You can use two levels of window functions:

SELECT Studentname,  Subject,  Marks, 
       RANK() OVER(PARTITION BY Studentname ORDER BY Sum_Marks DESC) Rank
FROM (SELECT er.*,
             SUM(Marks) OVER (PARTITION BY StudentName) as sum_marks
      FROM ExamResult
     ) er
ORDER BY Studentname
user_11440350
Related