SQL query to return record if the price is higher and the ID is lower than previous ID and price records

user_9319058 0
Viewed 87

I'm having issues writing a SQL query to return a record when I have a dataset similar to the following:

id price
32967 39
14675 40
26434 41
18495 42
19698 43
19090 44
9278 45
14932 46

I would like to return the id and price when the price is greater than previous price and the id is lower than previously low priced records. So with the above data I would like to return:

id price
32967 39
14675 40
9278 45

I truly appreciate the help. I'm to the point where my brain has shut off and I'm certain I'm missing something obvious. Know when to ask for help, right?

postgresql sql database
1 Answers

If I understand correctly, a cumulative min() does what you want:

select t.*
from (select t.*,
             min(id) over (order by price rows between unbounded preceding and 1 preceding) as prev_min_id
      from t
     ) t
where prev_min_id is null or id < prev_min_id;

Here is a db<>fiddle.

user_11440350
Related