mysql get max value after min value

In mysql table, I want to get the max value that comes after the min value.

SELECT * from `table`
result:

id  date                 value
--  -------------------  -----
1   2021-03-03 13:14:05  1.15
2   2021-03-03 13:14:06  1.32
3   2021-03-03 13:14:07  1.40
4   2021-03-03 13:14:08  1.38
5   2021-03-03 13:14:01  1.55
6   2021-03-03 13:14:02  1.60
7   2021-03-03 13:14:03  1.30
8   2021-03-03 13:14:04  1.10

but I have to sort by date with,

SELECT * from `table` ORDER by date
result:

id  date                 value
--  -------------------  -----
5   2021-03-03 13:14:01  1.55
6   2021-03-03 13:14:02  1.60 # this is not target row.
7   2021-03-03 13:14:03  1.30
8   2021-03-03 13:14:04  1.10 # min value
1   2021-03-03 13:14:05  1.15
2   2021-03-03 13:14:06  1.32
3   2021-03-03 13:14:07  1.40 # this is TARGET row.(max after min)
4   2021-03-03 13:14:08  1.38

After sort by date, I want to get max value(1.40) that comes after the min value(1.10)

id 6, value 1.60 is not target row. Because this max(1.60) is not comes after min value(1.10)

id 3, value 1.40 is target row. Because this max value(1.40) comes after min value(1.10) all values before the min value should be ignored. I want to get this row(id 3, value 1.40)

which sql query do I need?

Solution

You can run something like this:

select * from t
where vl = (
  select max(vl) from t
  where dt > (
    select dt from t where vl = (select min(vl) from t)
  )
);

+------+---------------------+------+
| id   | dt                  | vl   |
+------+---------------------+------+
|    3 | 2021-03-03 13:14:07 | 1.40 |
+------+---------------------+------+

The idea is to get the min value from table t first using select min(vl) from t.

Then, we get the date when we see that value using select dt from t where vl = (select min(vl) from t).

Then, we get the max value that shows up after that date using:

select max(vl) from t
  where dt > (
    select dt from t where vl = (select min(vl) from t)
  )

Then, we get the row that has that max value.