How do I get timestamp when minimum or maximum value occur with SQL?

Question

I have a database with columns timestamp and value. Logging is done periodically. Now I want to do daily aggregation. Have done it this way:

SELECT
    strftime("%Y-%m-%d", timestamp) AS "new_timestamp",
    AVG(value) as value_avg,
    MIN(value) as value_min,
    MAX(value) as value_max,
    COUNT(*) as num_samples,
    /* ... something for when max and min value occurs */
FROM my_table
GROUP BY "new_timestamp"

Q: How do I get timestamp/time when MIN or MAX has occurred in that day?

EDIT: In my specific case, if there are multiple min or max values it doesn't matter which one should be picked. I'm using SQLite.

Which dbms are you using? What's the expected result if the same max (or min) value exists twice during a day? — jarlh, Dec 28 '16 at 15:26
Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — O. Jones, Dec 28 '16 at 15:26
Add sample data and expected result your **Q** is not all that clear — Pரதீப், Dec 28 '16 at 15:27
@O.Jones, I will try examples from there. Didn't really know how to form a question for this. — gcerar, Dec 28 '16 at 16:23

score 0 · Answer 1 · answered Dec 28 '16 at 15:35

The use of strftime() suggests that you are using SQLite. If so, then a correlated subquery is probably the easiest way to do the calculation.

I think the following will work:

SELECT strftime('%Y-%m-%d', timestamp) AS "new_timestamp",
       AVG(value) as value_avg,
       MIN(value) as value_min,
       MAX(value) as value_max,
       COUNT(*) as num_samples,
       (SELECT t2.timestamp
        FROM my_table t2
        WHERE strftime('%Y-%m-%d', t2.timestamp) = strftime('%Y-%m-%d', t.timestamp)
        ORDER BY value desc
       ) as timestamp_max,
       (SELECT t2.timestamp
        FROM my_table t2
        WHERE strftime('%Y-%m-%d', t2.timestamp) = strftime('%Y-%m-%d', t.timestamp)
        ORDER BY value asc
       ) as timestamp_min
FROM my_table t
GROUP BY "new_timestamp"

How do I get timestamp when minimum or maximum value occur with SQL?

1 Answers1