SQL query : How to retrieve minimum value from top to that row in Oracle

Question

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

Rank	Amount	Calc Min (previous, current)
1	600	600
2	800	600
3	300	300
4	500	300
5	500	300
6	800	300
7	200	200
8	550	200

Thanks in Advance Ash

Answer 1

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;

Answer 2

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