Oracle SQL: Previous MAX sore in all previous quarters

user_9917510 0
Viewed 50

Best method used to get MAX previous scores for each ID over all the Quarters.

Given:

    ID  QTR SCORE 
    21  1   3 
    21  2   5 
    21  3   3 
    21  4   3 
    41  1   2 
    41  2   2 
    41  3   4 
    41  4   2

Expected:

    ID  QTR PREV_MAX_SCORE
    21  1   3   
    21  2   5   
    21  3   5   
    21  4   5   
    41  1   2   
    41  2   2   
    41  3   4
    41  4   4
oracle sql
2 Answers

You can try to use MAX window function to make it.

CREATE TABLE T(
  ID int,
  QTR int,
  SCORE int
);


insert into t values (21,1,3); 
insert into t values (21,2,5); 
insert into t values (21,3,3); 
insert into t values (21,4,3); 
insert into t values (41,1,2); 
insert into t values (41,2,2); 
insert into t values (41,3,4); 
insert into t values (41,4,2);

Query 1:

SELECT t1.ID,
       t1.QTR,
       max(SCORE) over(partition by ID order by QTR) SCORE
FROM T t1

Results:

| ID | QTR | SCORE |
|----|-----|-------|
| 21 |   1 |     3 |
| 21 |   2 |     5 |
| 21 |   3 |     5 |
| 21 |   4 |     5 |
| 41 |   1 |     2 |
| 41 |   2 |     2 |
| 41 |   3 |     4 |
| 41 |   4 |     4 |
user_51760710

A correlated subquery might be used as :

with tab(ID,QTR,SCORE) as
(
 select 21,  1,   3 from dual union all
 select 21,  2,   5 from dual union all
 select 21,  3,   3 from dual union all
 select 21,  4,   3 from dual union all
 select 41,  1,   2 from dual union all
 select 41,  2,   2 from dual union all
 select 41,  3,   4 from dual union all
 select 41,  4,   2 from dual
)
select t.id, t.qtr,
       ( select max(score) from tab where qtr <= t.qtr and id = t.id ) as prev_max_score
  from tab t;

Rextester Demo

user_58413060
Related