How to show every max value in mysql?

问题:

I've multiple values with different timestamps like the following:

10   01:01:00
20   01:35:00
30   02:10:00
05   02:45:00
12   03:05:00
21   03:30:00
10   04:06:00
40   05:15:00

I don't have a column with which I can group by and find max. I want to get the records with max values like 30,21, and 40. The data is always in this format, like value increasing and then starts from zero again. What query will help me to find these records?

---

To clarify, it's sorted by the timestamp, and I want to get the timestamps for the local maxima, the rows where the next row has a lesser value:

value    tmstmp
-----   --------
 10     01:01:00
 20     01:35:00
 30     02:10:00  <-- this one since next value is 5 (< 30).

 05     02:45:00
 12     03:05:00
 21     03:30:00  <-- this one since next value is 10 (< 21).

 10     04:06:00
 40     05:15:00  <-- this one since next value is 40 (< infinity).

回答1:

Somehow your question is not clear to me. Assume that first column name is "value" and second column name is "timestamp".

Select Max(value) from group by timestamp.

回答2:

This answer might be a bit late, however i think i have found the solution

SELECT * FROM temp t1 WHERE value > 
  IFNULL(
    (SELECT value FROM temp t2
    WHERE t2.tmstmp > t1.tmstmp ORDER BY t2.tmstmp ASC limit 1),
   -1
  ) 
ORDER BY tmstmp ASC

To clarify: I find the values where the value is greater than the next value in the row. To also get the final value I have added an IFNULL around the subquery to make sure the subquery will then return -1

The only problem i see is when the time goes over to the next day, that's why i hope you can have a date appended to it as well.

Hopefully this will still help others