Search code examples
sqlpostgresqlgreatest-n-per-grouppostgresql-8.3

SELECT and GROUP BY

I just want to filter few columns from the table according to MAX(ts). ts = timestamp. And everything is OK if I select only two columns - deviceid and ts:

SELECT deviceid, MAX(ts) 
FROM device_data 
GROUP BY deviceid

and the result:

enter image description here

but I also need two columns more - longitude and lattitude. I have the problem if I select longitude and lattitude because they have to appear in GROUP BY and I get too much results with the same deviceid:

enter image description here

How can I avoid inserting longitude and lattitude in GROUP BY?

Solution

  • There are several solutions for this. One is to use window functions to get the first longitude, latitude, ...etc within a partition of same deviceid when ordered by descending date.

    Then you will get duplicates, which you can remove with distinct:

    SELECT DISTINCT deviceid, 
       FIRST_VALUE(longitude) OVER win AS longitude,
       FIRST_VALUE(latitude) OVER win AS latitute,
       FIRST_VALUE(ts) OVER win AS ts 
FROM   device_data
WINDOW win AS (PARTITION BY deviceid ORDER BY ts DESC);