mysql get max value after min value

Question

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?

Can you edit the question to more clearly show the target result? — Strawberry, Mar 03 '21 at 23:36

score 2 · Accepted Answer · answered Mar 03 '21 at 23:41

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.

score 0 · Answer 2 · answered Mar 03 '21 at 23:31

0

I would use a subquery and a limit. The tablename and column name are just an example, so adjust it to your proper table names and columns. Good luck

select * from table 
inner join (
    select id as minvalid, min(value) as minvalue from table 
) subQuery on value > minvalue 
limit 1

answered Mar 03 '21 at 23:31

Oliver M Grech

3,071
1
21
36

This is incorrect. `select id as minvalid, min(value)` may return a `minvalid` that is unrelated to the row that contains `min(value)`. You'll need to add another layer to fetch the related ID, as zedfoxus has done. – rjdown Mar 03 '21 at 23:49
Confirmed and agreed, thanks for pointing it out. – Oliver M Grech Mar 04 '21 at 09:28

mysql get max value after min value

2 Answers2