Conditionally selecting rows in SQLITE based on row order-CodePudding

To simplify things:

I have a table with columns: score and timestamp

I need to select each row that score is a new high, when sorted by time. E.g.

Score	Timestamp
6	1
3	2
3	3
5	4
7	5
10	6
8	7
9	8
10	9
11	10

So as timestamp goes on, every time the "score" reaches a new high, that gets row gets selected. In this example the bolded rows get selected. I'm using SQLite.

CodePudding user response：

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

CodePudding user response：

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.