CODEWITHSUNDEEP
sqldatabaseoracle-databaseselectgreatest-n-per-group
A.Nassar
A.Nassar

Reputation: 27

Get the maximum value of values for each category in SQL(oracle)

Let us say we have the current table of subjects and marks of each students in that subject.

Table: Grades

Math:    ANDY  80
Math:    RYAN  70
Math:    SARAH 95 
Science: RYAN  60
Science: ANDY  90
Science: SARAH 75

I want to write a query to get the name of the subject, who has the highest mark, and what is it.

the output of the query should be as the following:

Math    SARAH 95
Science ANDY  90

what would be the sql (in oracle) to get this desired output? preferably without using a windows function.

Upvotes: 0

Views: 536

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

In Oracle, you can do this with aggregation and keep:

select subject,
       max(mark),
       max(student) keep (dense_rank first order by mark desc)
from grades
group by subject;

Note: This only keeps one student per subject, even when there are ties. You could use listagg() to get all of them.

Upvotes: 0

GMB
GMB

Reputation: 222462

You can use analytical function rank():

select *
from (
    select g.*, rank() over(partition by subject order by mark desc) rn
    from grades
) t
where rn = 1

In the subquery, the analytical function ranks each record by descending mark within groups having the same subject. Then, the outer query filters the top record per group. If there are ties, they are all returned (you can use row_number() to avoid that, or add another sorting criteria to the order by clause).

Upvotes: 2

Related Questions