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()

Answer 2

You will have do one more group by and a join to get the desired results. This should do:

select q1.*, q2.roll from 

(
select school, class, max(total) as max from 
(
select school,class,roll,sum(marks) as total from students group by school,class,roll order by school, class, total desc
)q3 group by school, class
)q1

LEFT OUTER JOIN

(select school,class,roll,sum(marks) as total from students group by school,class,roll order by school, class, total desc)q2

ON (q1.max = q2.total) AND (q1.school = q2.school) AND (q1.class = q2.class)

Answer 3

We will have to build on the query that you have provided :

1. The given query will give you the marks per class per roll. To find out the highest total achieved per class, you will have to remove roll number from the select and then group on this query.

2. Now we know the school, class and highest total per class per school. You just have to find out the roll number corresponding to this total. For that, a join will be needed.

The final query will look like this :

select a.school, a.class, b.roll, a.highest_marks from
    (select q.school as school, q.class as class, max(q.total) as highest_marks from(select school, class, roll, sum(marks) as total from students group by school, class, roll)q group by school, class)a
    join
    (select school, class, roll, sum(marks) as total from students group by school, class, roll)b
    on (a.school = b.school) and (a.class = b.class) and (a.highest_marks = b.total)