Roger Wayne Roger Wayne - 2 months ago 11
MySQL Question

Retrieve first and last entry of hour with MySQL

I have database to storing temperatures. There are three to five temperatures per hour and i want to get first and last temperature of an hour but i can't create suitable subquery.

Here is the main query to get temperatures by hour.

SELECT HOUR(time) AS h, max(temp_in) as max, min(temp_in) as min, count(temp_in) as count
FROM tbl_temps
GROUP BY h;


enter image description here

Answer

You can use a union and subquery

select *  from 
my_table where (HOUR(time),MINUTE(time)) in (
        select HOUR(time), min(MINUTE(time))
        from my_table
        group by HOUR(time)
 )
union 
select *  from 
my_table where (HOUR(time),MINUTE(time)) in (
        select HOUR(time), max(MINUTE(time))
        from my_table
        group by HOUR(time)
)
order by HOUR(time), MINUTE(time)
Comments