user526206 user526206 - 3 months ago 9
SQL Question

How to get min value in postgres sql

I have few records and i want to create a query to give hourly records of a each divece battery level

What i did from timestamp field i extract date and select min function to get low value but as extract hour from timestamp is not aggragate function so i need to add in group by which now given me duplicate records.
Here is my sql:

select extract(hour from observationtime) as hour,
deviceid,
min(batterylevel) as batterylevel
from smartvakt_device_report
where batterylevel!=''
and deviceid!=''
and observationtime between '2016-02-02' and '2016-03-02'
group by observationtime,deviceid
order by observationtime ASC, deviceid ASC


Here is above query output:
enter image description here

Here are actual records:
enter image description here

Can someone suggest how i can remove these duplicate

Answer

Change Group by column Order to first Group by deviceid then by the hour using the same function extract(hour from observationtime).

    SELECT
    deviceid, 
    extract(hour from observationtime) AS hour,
    min(batterylevel) AS batterylevel 
    FROM smartvakt_device_report
    WHERE
    batterylevel!='' 
    AND deviceid!='' 
    AND observationtime BETWEEN '2016-02-02' 
    AND '2016-03-02' 
    GROUP BY
    deviceid,
    extract(hour from observationtime)
    ORDER BY 
    extract(hour from observationtime) ASC, 
    deviceid ASC