## Question and Answer

## You Asked

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

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

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'

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:

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