alixaxel's solution to "Reduce record count in MySQL temp sensor table "

The following will give you one measurement per station/hour (not necessarily the one with the highest temp):<\/p>\n

SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> <\/span>`<\/span>TempLog<\/span>`<\/span> <\/span>GROUP<\/span> <\/span>BY<\/span> <\/span>`<\/span>Station<\/span>`<\/span>,<\/span> <\/span>strftime<\/span>(<\/span>'%Y%m%d%H', `logDate`);<\/span>\n<\/pre><\/div>
The following will give you one measurement per station/hour (not necessarily the one with the highest temp): SELECT * FROM `TempLog` GROUP BY `Station`, strftime('%Y%m%d%H', `DATE_FORMAT(`logDate`);`, '%Y%m%d%H');
The following will give you one measurement per station/hour (not necessarily the one with the highest temp): SELECT * FROM `TempLog` GROUP TempLog GROUP BY ` Station`, , DATE_FORMAT(`(logDate`, '%, '%Y%m%d%H');
The following will give you one measurement per station/hour (not necessarily the one with the highest temp): SELECT * FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H');'); To select the one with the highest temperature within a station/hour: SELECT *, MAX(temp) AS max FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); ---
The following will give you one measurement per station/hour (not necessarily the one with the highest temp): SELECT * FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); To select the one with the highest temperature within a station/hour: SELECT *, MAX(temp) AS max FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); ---'); --- To delete all but one record per hour/station: DELETE FROM TempLog WHERE ID NOT IN ( SELECT ID FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); );
The following will give you one measurement per station/hour (not necessarily the one with the highest temp): SELECT * FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); To select the one with the highest temperature within a station/hour: SELECT *, MAX(temp) AS max FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); --- To delete all but one record per hour/station: DELETE FROM TempLog WHERE ID NOT IN ( SELECT ID FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); );
The following will give you one measurement per station/hour (not necessarily the one with the highest temp): SELECT * FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); To select the one with the highest temperature within a station/hour: SELECT *, MAX(temp) AS max FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); --- To delete all but one record per hour/station: DELETE TempLog WHERE ID NOT IN ( SELECT ID FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); );'); ); Similarly, but chosing to maintain the records with the highest temperature per hour/station: DELETE FROM TempLog WHERE ID NOT IN ( SELECT ID FROM ( SELECT ID, MAX(temp) from TempLog GROUP BY `Station`, DATE_FORMAT(logDate, '%Y%m%d%H') ) );
The following will give you one measurement per station/hour (not necessarily the one with the highest temp): SELECT * FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); To select the one with the highest temperature within a station/hour: SELECT *, MAX(temp) AS max FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); --- To delete all but one record per hour/station: DELETE TempLog WHERE ID NOT IN ( SELECT ID FROM TempLog GROUP BY Station, DATE_FORMAT(logDate, '%Y%m%d%H'); ); Similarly, but chosing to maintain the records with the highest temperature per hour/station: DELETE FROM TempLog WHERE ID NOT IN ( SELECT ID FROM ( SELECT ID, MAX(temp) from TempLog GROUP BY ` Station`, , DATE_FORMAT(logDate, '%Y%m%d%H') ) );

User: alixaxel

Question: Reduce record count in MySQL temp sensor table

Back to question