SELECT and GROUP BY

Question

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?

Which database? Im thinking u need nested queries but the syntax may vary dependent on database used. — Stephen Quan, Nov 26 '17 at 19:00
Sorry for none of information about DBMS. I am using PostgreSQL. — Damian Kowalewski, Nov 26 '17 at 19:00

score 1 · Accepted Answer · answered Nov 26 '17 at 19:17

1

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);

answered Nov 26 '17 at 19:17

trincot

317,000
35
244
286

Hi, I am trying to run above SELECT DISTINCT but I am getting an error at first OVER: syntax error at or near OVER – Damian Kowalewski Nov 26 '17 at 19:26
1

Which version of postgresql are you running on? – trincot Nov 26 '17 at 19:27
1

@DamianKowalewski: if you get an error with that query you are either not using Postgres or using a **very**, **very** old version (and unsupported) of Postgres – Nov 26 '17 at 19:31
PostgreSQL 8.3.23. I have tried solution sent by @penguin and it works. :) – Damian Kowalewski Nov 26 '17 at 19:33
1

@DamianKowalewski You should upgrade your Postgres version now. 8.3 has been [out of support](https://www.postgresql.org/support/versioning/) for nearly 5 years now – Nov 26 '17 at 19:34
@trincot your solutions works on postgresql 10 also :) thank you – Damian Kowalewski Nov 26 '17 at 19:49
Yes, you should really move away from 8.x. You're welcome ;-) – trincot Nov 26 '17 at 20:37

score 1 · Answer 2 · edited Nov 26 '17 at 19:33

1

You can create a table that contains device_id and max of ts per device_id, then do an inner join with original device_data to get desired result.

Unless you have multiple rows with same device_id and max(ts), this should work.

SELECT a.deviceid, a.ts, a.longitude, a.lattitude
FROM device_data a
INNER JOIN
(SELECT deviceid, MAX(ts) as max_ts
FROM device_data 
GROUP BY deviceid) b ON a.ts = b.max_ts
GROUP BY 1,2,3,4;

edited Nov 26 '17 at 19:33

Billal Begueradj

20,717
43
112
130

answered Nov 26 '17 at 19:22

penguin

1,267
14
27

I will create another one ask so you can help me also in other subject of sql :) – Damian Kowalewski Nov 26 '17 at 19:54

SELECT and GROUP BY

2 Answers2