Skip to Main Content
  • Questions
  • Comparing previous max value in SQL query

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Daniel.

Asked: November 18, 2020 - 4:48 pm UTC

Answered by: Chris Saxon - Last updated: November 18, 2020 - 5:28 pm UTC

Category: SQL - Version: Oracle 12c

You Asked

Assume I have a column in a table "scores".

score
7
5
3
10
9
11



Using just a query, how would I get the output:

score  new_high_score
7      Y
5      N
3      N
10     Y
9      N
11     Y



Walkthrough:
7 is the first so it's a 'Y'
5 < 7, so 'N'
3 < 7, so 'N'
10 > 7, so 'Y'
9 < 10, so 'N'
11 > 10, so 'Y'



and we said...

First up: for this work you must have another column stating the order for these values, for example score_date. Without this no solution will work!

Assuming you have this, you can get the running max ( score ) by adding the over clause and sorting this ordering column.

Then compare this to the score for each row to set the high score as appropriate:

with rws as (
  select date'2020-02-02' + level score_date,
         round ( dbms_random.value ( 1, 10 ) ) score
  from   dual
  connect by level <= 10
)
  select score, 
         case 
           when max ( score ) over ( 
             order by score_date
           ) <= score 
           then 'Y'
           else 'N'
         end high_score,
         score_date
  from   rws;
  
SCORE    HIGH_SCORE    SCORE_DATE             
       3 Y             03-FEB-2020 00:00:00    
       7 Y             04-FEB-2020 00:00:00    
       2 N             05-FEB-2020 00:00:00    
       1 N             06-FEB-2020 00:00:00    
       4 N             07-FEB-2020 00:00:00    
       1 N             08-FEB-2020 00:00:00    
       2 N             09-FEB-2020 00:00:00    
       6 N             10-FEB-2020 00:00:00    
       8 Y             11-FEB-2020 00:00:00    
       1 N             12-FEB-2020 00:00:00

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.