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'
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