0

I want to select the latest record of each hour in a given date, based on the datetime column 'reading_on' in my table. What is the best way to do it? I already tried group by HOUR(reading_on) with HAVING("max(HOUR(reading_on))"), but I didn't get the proper result.

Below is my table structure

enter image description here

1

That's a groupwise maximum.
You may plump for a null-self-join:

SELECT  ir1.*
FROM inverter_readings IR1
LEFT JOIN inverter_readings IR2 ON
DATE(IR1.READING_ON) = DATE(IR2.READING_ON) AND
HOUR(IR1.READING_ON) = HOUR(IR2.READING_ON) AND
IR1.READING_ON  < IR2.READING_ON
WHERE IR2.id IS NULL

sqlfiddle demo.

0

To lookup the maximum id value for each hour on May 10, 2014

SET @GivenDate = '2014-05-10';
SET @dt1 = @GivenDate + INTERVAL 0 SECOND;
SET @dt2 = @GivenDate + INTERVAL 1 DAY - INTERVAL 1 SECOND;
SELECT A.HourRange,B.* FROM
(
    SELECT MAX(id) id,HourRange
    (
        SELECT id,(DATE(@reading_on) + INTERVAL HOUR(@reading_on) HOUR HourRange
        FROM inverter_readings WHERE reading_on >= @dt1 AND reading_on <= @dt2
    ) AA
    GROUP BY HourRange
) A LEFT JOIN inverter_readings B USING (id);

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.