Conditionally selecting rows based on order

vpvp 0
Viewed 48

I have a table with columns score and timestamp. I need to select each row that score is a new high, when sorted by timestamp (bold rows):

score timestamp
6 1
3 2
3 3
5 4
7 5
10 6
8 7
9 8
10 9
11 10
sqlite database sql
2 Answers
select   records
        ,min(Timestamp) as Timestamp
from    (
         select max(Score) over(order by timestamp) as records, Timestamp
         from t
         ) t
group by records
records Timestamp
6 1
7 5
10 6
11 10

Fiddle

DannySlor0

You may try the following (Supposing that timestamp is unique):

Select T.Score, T.Timestamp
From score_tbl T 
Where NOT Exists(Select 1 From score_tbl D Where D.Timestamp < T.Timestamp And D.Score >= T.Score)
Order By T.Timestamp

See a demo.

userGpjR0
Related