SQL Query for Daily Minimum and Maximum Values with Timestamps
Question details
Create a daily SQL report showing the first, minimum, and maximum measurements and their associated timestamps.
- Product
- SQL database
- Device & OS
- not provided
- Scenario
- A report groups measurement records by date and excludes zero readings before identifying daily results.
- Observed behavior
- The desired report includes the earliest qualifying reading and the timestamps associated with each day's minimum and maximum measurements.
Identify your table and the columns for date, timestamp, measurement, and a unique record ID. Confirm whether negative measurements should be included and how ties should be resolved.
Use SQL window functions to return daily readings and timestamps
Use this approach when you need the earliest qualifying reading and the timestamps of the daily minimum and maximum measurements.
The query filters readings before ranking them. It selects the earliest timestamp when multiple records share a minimum or maximum value, using a unique record ID to break any remaining ties.
Open your database query editor and replace readings, Daily_Date, Daily_Time, Measurement, and Reading_ID in the query with your actual table and column names. Daily_Time should contain the time or timestamp of each reading, and Reading_ID should uniquely identify a record.
Run this query: WITH ranked AS ( SELECT CAST(Daily_Date AS DATE) AS report_date, Daily_Time, Measurement, ROW_NUMBER() OVER ( PARTITION BY CAST(Daily_Date AS DATE) ORDER BY Daily_Time, Reading_ID ) AS first_rank, ROW_NUMBER() OVER ( PARTITION BY CAST(Daily_Date AS DATE) ORDER BY Measurement ASC, Daily_Time, Reading_ID ) AS min_rank, ROW_NUMBER() OVER ( PARTITION BY CAST(Daily_Date AS DATE) ORDER BY Measurement DESC, Daily_Time, Reading_ID ) AS max_rank FROM readings WHERE Measurement > 0 ) SELECT report_date, MAX(CASE WHEN first_rank = 1 THEN Daily_Time END) AS first_time, MAX(CASE WHEN first_rank = 1 THEN Measurement END) AS first_value, MIN(Measurement) AS min_value, MAX(CASE WHEN min_rank = 1 THEN Daily_Time END) AS min_time, MAX(Measurement) AS max_value, MAX(CASE WHEN max_rank = 1 THEN Daily_Time END) AS max_time FROM ranked GROUP BY report_date ORDER BY report_date;
Review the results grid for the first, minimum, and maximum readings on each date. If negative readings should be included while zeros are excluded, change WHERE Measurement > 0 to WHERE Measurement <> 0.

Review Daily SQL Reports in WPS Spreadsheets
Export your SQL results and review them in WPS Spreadsheets, a free, lightweight Office alternative with a familiar interface. Its Microsoft Excel format support makes it easy to move existing reports into WPS Office. Run the SQL query in your database first.

Frequently Asked Questions
How do I exclude zeros but keep negative measurements?
In the query's WHERE clause, use Measurement <> 0 instead of Measurement > 0. The greater-than filter excludes both zeros and negative values.
Which timestamp is returned when multiple readings share the minimum?
The query orders tied minimum readings by Daily_Time and then Reading_ID, so it returns the earliest timestamp. Change the ORDER BY clause in the min_rank window if you prefer a different tie-breaker.
Does the first reading mean the smallest measurement?
No. In this query, first means the earliest qualifying record by Daily_Time. The smallest measurement is returned separately as min_value with its associated min_time.
Why is a date missing from the report?
The WHERE clause removes nonqualifying rows before grouping, so a date with no qualifying measurements does not appear. To show every date, join the results to a calendar table.




