Question 186475 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Min Max Historic Values

I have a MYSQL table with this format:

Insurance Date Value
A 2020-01-01 2
A 2020-02-01 5
A 2020-03-01 4
A 2020-04-01 6
A 2020-05-01 8
A 2020-06-01 1
A 2020-07-01 3
A 2020-08-01 7
A 2020-09-01 9
A 2020-10-01 3
A 2020-11-01 5
A 2020-12-01 6
B 2020-01-01 5
...

I want SELECT Min and Max from this way (historic value)

in date 2020-01-01 Min 2 Max 2
in date 2020-02-01 Min 2 Max 5
in date 2020-03-01 Min 2 Max 5
in date 2020-04-01 Min 2 Max 6
In date 2020-05-01 Min 2 Max 8
In date 2020-06-01 Min 1 Max 8
In date 2020-07-01 Min 1 Max 8
In date 2020-08-01 Min 1 Max 8
In date 2020-09-01 Min 1 Max 9
In date 2020-10-01 Min 1 Max 9
In date 2020-11-01 Min 1 Max 9
In date 2020-12-01 Min 1 Max 9

T try to fin the solution, but only I get min and max value from a between dates, but not month to month with reference at last months.

Can you help me? THANKS

Tags:

2

Answers


  1. 0

    Try the follow query to get min and max value of a date from table: –

    SELECT DISTINCT date, MAX(value) AS max_value, MIN(value) as min_value
FROM table_name group by data;
    Login or Signup to reply.
  2. 0

    You seem to be looking for a window min/max over all preceding rows of the same insurance. You can use window functions (available in MySQL 8.0):

    select t.*,
    min(val) over(partition by insurance order by dt) min_val_so_far,
    max(val) over(partition by insurance order by dt) max_val_so_far
from mytable t

    Demo on DB Fiddle:

    insurance dt val min_val_so_far max_val_so_far
    A 2020-01-01 2 2 2
    A 2020-02-01 5 2 5
    A 2020-03-01 4 2 5
    A 2020-04-01 6 2 6
    A 2020-05-01 8 2 8
    A 2020-06-01 1 1 8
    A 2020-07-01 3 1 8
    A 2020-08-01 7 1 8
    A 2020-09-01 9 1 9
    A 2020-10-01 3 1 9
    A 2020-11-01 5 1 9
    A 2020-12-01 6 1 9
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search