SQL - Selecting rows with a column value greater than the rows before it

Question

I'm curious on the best way to write a query.

I have a table of ids and values. I would like to exclude rows where val is less than val in all of the rows with a lower ID.

I was playing with joining this table to itself on id-1 but that didn't work all of the time.

Some sample data

CREATE TEMP TABLE new_temp_table (
    id integer, 
    val integer
);

INSERT INTO new_temp_table (id, val)
VALUES (0, 300),
       (1, 150),
       (2, 100), 
       (3, 200),
       (4, 320),
       (5, 120),
       (6, 220),
       (7, 340);

I want the following output.

--- id --- val 
--- 0  --- 300
--- 4  --- 320
--- 7  --- 340 

Any help/direction would be appreciated.

Answer 1

With NOT EXISTS:

select t.* from new_temp_table t
where not exists (
  select 1 from new_temp_table
  where id < t.id and val > t.val
)  

See the demo.
Results:

| id  | val |
| --- | --- |
| 0   | 300 |
| 4   | 320 |
| 7   | 340 |