Compare Previous column data with the next column data

Question

I have a sales table with the following columns. I want to select the rows where sale price is increasing and skip those decrease sale price in which the sale price of above row is increase. e.g. in the following table, I would like to have all rows except row having saleid=4

+--------+--------+-----------+
| SaleId | ItemId | SalePrice |
+--------+--------+-----------+
| 1      | 987    | 12        |
+--------+--------+-----------+
| 2      | 678    | 13        |
+--------+--------+-----------+
| 3      | 987    | 15        |
+--------+--------+-----------+
| 4      | 542    | 11        |
+--------+--------+-----------+
| 5      | 678    | 16        |
+--------+--------+-----------+

I have tried using inner join. But it shows nothing. Here is the query I have wrote:

select s1.* from saletable s1
join saletable s2 on s1.saleid = s2.saleid
where s1.saleprice<s2.saleprice

Answer 1

Consider the following solution using running max

select t.*
from
(
    select *, max(SalePrice) over (order by SaleId) runningMaxSalePrice
    from testdata
) t
where t.SalePrice >= t.runningMaxSalePrice

This solution skips more than one consecutive row with decreasing SalePrice.

DBFdiddle DEMO