The first value of each month in SQL

Question

I have a database containing data values ​​with a timestamp, with multiple samples per day, i.e.

05/01/11 01:00:00 - 1.23 05/01/11 01:12:34 - 0.99 .... 05/01/11 23:59:59 - 2.34 05/02/11 00:11:22 - 4.56 

etc.

I am trying to get a list of the first sample of each month. I managed to get a list of each sample on the 1st of each month:

 SELECT "RecordTime", "FormattedValue" FROM CDBHistoric WHERE "Id" = 12345 AND EXTRACT (DAY FROM "RecordTime") = 1 

This gives data such as

 03/01/11 00:00:01 - 1.23 03/01/11 00:12:34 - 0.99 .... 04/01/11 00:00:34 - 2.34 04/01/11 00:11:22 - 4.56 

but I could not get the first value of each group.

Answer 1

Here is a solution for SQL Server. The idea is to use a correlated subquery to identify a minimum RecordTime for each year and month. It should be easy to translate the idea to your DBMS taste.

 WITH Data AS ( SELECT CONVERT(DATETIME, '1-May-2011 01:00:00') AS RecordTime, 1.23 AS FormattedValue UNION ALL SELECT CONVERT(DATETIME, '1-May-2011 01:12:34'), 0.99 UNION ALL SELECT CONVERT(DATETIME, '1-May-2011 23:59:59'), 2.34 UNION ALL SELECT CONVERT(DATETIME, '2-May-2011 00:11:22'), 4.56 ) SELECT D1.* FROM Data D1 WHERE D1.RecordTime = (SELECT MIN(RecordTime) FROM Data D2 WHERE YEAR(D2.RecordTime) = YEAR(D1.RecordTime) AND MONTH(D2.RecordTime) = MONTH(D1.RecordTime)) 

Edit: If your dataset is large, then combining into a subquery should be much faster, but it may be more difficult to translate your taste of the DBMS:

 SELECT D1.* FROM Data D1 INNER JOIN ( SELECT MIN(D2.RecordTime) AS FirstMonthlyRecordTime FROM Data D2 GROUP BY YEAR(D2.RecordTime), MONTH(D2.RecordTime) ) AS SubQuery ON D1.RecordTime = SubQuery.FirstMonthlyRecordTime 

Edit 2: The following code is the standard version of my first SQL-92 query above (tested on mySQL):

 SELECT T1.RecordTime, T1.FormattedValue FROM CDBHistoric T1 WHERE T1.RecordTime = (SELECT MIN(T2.RecordTime) FROM CDBHistoric T2 WHERE EXTRACT(YEAR FROM T1.RecordTime) = EXTRACT(YEAR FROM T2.RecordTime) AND EXTRACT(MONTH FROM T1.RecordTime) = EXTRACT(MONTH FROM T2.RecordTime))