I need help to write a query in Oracle to get minimum value for each row, comparing the current row amount with the previous minimum value.

In another word, calculate minimum value for each row from top to that row, dataset for minimum function is from the first row to the current row.

For example: retrieve Min(previous, current) value for each row as below

Thanks in Advance Ash

CodePudding user response：

You are looking for the analytic function MIN OVER.

select
  rank,
  amount,
  min(amount) over (order by rank) as min_amount_so_far
from mytable
order by rank;

CodePudding user response：

I've gone off the assumption that you want these ordered on Rank. With that assumption, we can use a case expression along with lag to create these results:

select t.Rank
    , t.Amount
    , case
        when t.Rank = 1 then t.Amount
        when t.Amount < lag(t.Amount, 1, 0) over (order by t.Rank) then t.Amount else lag(t.Amount, 1, 0) over (order by t.Rank)
      end "Calc Min (previous, current)"
from tbl t
order by t.Rank

The first case of t.Rank = 1 is because you will get a result of 0 from lag on the first row.

CodePudding user response：

You can also solve this using MATCH_RECOGNIZE:

SELECT rank, amount, min_amount
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY Rank
  MEASURES
    MIN(Amount) AS min_amount
  ALL ROWS PER MATCH
  PATTERN (^ any_row  )
  DEFINE any_row AS 1 = 1
)

Which, for the sample data:

CREATE TABLE table_name (Rank, Amount) AS
SELECT 1, 600 FROM DUAL UNION ALL
SELECT 2, 800 FROM DUAL UNION ALL
SELECT 3, 300 FROM DUAL UNION ALL
SELECT 4, 500 FROM DUAL UNION ALL
SELECT 5, 500 FROM DUAL UNION ALL
SELECT 6, 800 FROM DUAL UNION ALL
SELECT 7, 200 FROM DUAL UNION ALL
SELECT 8, 550 FROM DUAL;

Outputs:

RANK	AMOUNT	MIN_AMOUNT
1	600	600
2	800	600
3	300	300
4	500	300
5	500	300
6	800	300
7	200	200
8	550	200

db<>fiddle here