Finding the first row in a group using Hive

Question

For a student database in the following format:

Roll Number | School Name | Name | Age | Gender | Class | Subject | Marks

how to find out who got the highest for each class? The below query returns the entire group, but I am interested in finding the first row in the group.

SELECT school,
       class,
       roll,
       Sum(marks) AS total
FROM   students
GROUP  BY school,
          class,
          roll
ORDER  BY school,
          class,
          total DESC; 

Answer 1

Another way using row_number()

select * from (
    select *, 
    row_number() over (partition by school,class,roll order by marks desc) rn
    from students
) t1 where rn = 1

If you want to return all ties for top marks, then use rank() instead of row_number()