CODEWITHSUNDEEP
sqlplsqlgroup-bymax
sayhaha
sayhaha

Reputation: 789

plsql compare and get max value

Student    Subj   Period  Score
-------------------------
A          Math   100        50
A          Hist   100        100
A          Sci    200        70
B          Math   100        50
B          Hist   100        50

I'm trying to query for the highest score for an each student.

If scores are all same (such as 'B' then pick one on the bottom. If that's not possible, just pick anything.

I'm having a hard time using max() and min() and group by to get a correct result. Result should look like this

Student    Subj   Period    Score
-------------------------
A          Hist   100       100
B          Hist   100       50

Upvotes: 0

Views: 4787

Answers (2)

dbenham
dbenham

Reputation: 130899

Gordon's answer certainly works very well, and it uses ANSI syntax that is supported by a number of database engines.

Oracle has an additional syntax that I don't think is ANSI, but it eliminates the need for any subquery. It performs at least as well as Gordon's answer (it may be slightly faster, but I'm not sure). It uses a KEEP LAST extension to aggregate functions.

select student,
       max(subj)   keep( dense_rank last order by score, period ) as subj,
       max(period) keep( dense_rank last order by score, period ) as period,
       max(score) as score
  from grades
 group by student
;

Note - Unless you explicitly want to provide random results, you should always structure your query to give determinate answers, meaning that a given set of data will always give the same answer, regardless how the data was loaded. I structured the above query to order by the highest score, followed by the highest period. If there is still a tie then the max(subj) is returned.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You want to use analytic functions (I assume this is in Oracle from the mention of pl/sql):

select Student, Subj, Period, Score
from (select t.*,
             row_number() over (partition by student order by score desc) as seqnum
      from t
     ) t
where seqnum = 1

Upvotes: 2

Related Questions