Others

SQL Query for Daily Minimum and Maximum Values with Timestamps

Olivia MillerOlivia Miller Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open your SQL editor

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.

2
Run the daily report query

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;

3
Check the filter and results

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.

Use SQL window functions to return daily readings and timestamps
Dates and column names: Days with no qualifying readings are omitted. SQL syntax can vary by database; adjust the date cast or quote identifiers as required. Avoid naming a column Time, which may be reserved in your SQL dialect.
Free Microsoft Office alternative

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.

Review daily minimum and maximum readings in a spreadsheetOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for a smooth transitionWork with a free, lightweight Office suite
microsoft office alternative - wps office

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.