1

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

MT0
  • 143,790
  • 11
  • 59
  • 117
Ash020292
  • 29
  • 2

2 Answers2

3

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;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 1
    @ARC - then you need to take a 30 minute break and think a little more generally about "analytic functions" and what they REALLY do. Whenever you need anything "from the top row until the current one", your first thought should be "is there an analytic version of the function I would have used for the entire table?" –  May 20 '22 at 23:22
0

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

MT0
  • 143,790
  • 11
  • 59
  • 117