0

There is a messages table in my postgreSql DB. The table has names: deviceId, timestamp, message. deviceId is identical, it indicates the id of existing device.

deviceId         timestamp                      message
dev12345         2020-01-02 21:01:02            hello Jack
dev12229         2020-01-02 12:03:02            good
dev22245         2020-01-01 12:01:05            hello Mary
dev12345         2020-01-01 11:03:02            good morning
...              ...                            ...

Assume there are total 100 devices, each device has send many messages at different time. I want to select the last messages send by some devices. For example, the last messages send by dev12345 and dev22245: hello Jack and hello Mary

Can you tell me how to do that using postgreSql command?

Thanks

GMB
  • 216,147
  • 25
  • 84
  • 135
lei lei
  • 1,753
  • 4
  • 19
  • 44
  • 1
    https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group https://stackoverflow.com/questions/28085468/postgresql-extract-last-row-for-each-id – Vladimir Baranov Mar 03 '20 at 00:49

2 Answers2

1

In Postgres, just use distinct on:

select distinct on (deviceId) t.*
from mytable t
where device id in ('dev12345', 'dev22245')
order by deviceId, timestamp desc

The distinct on (deviceId) clause indicates the database that we just want one record per device; the order by clause controls which row is retained in each group (here, the sort gives the priority to the most recent record per device).

GMB
  • 216,147
  • 25
  • 84
  • 135
1

You can use windows function (row_number) and CTE combination: 

WITH sub as (
select deviceId,message ,row_number() OVER (partition by deviceId  order by ts desc) as num  FROM tab )
SELECT deviceId,message FROM sub where num= 1
Anthony Sotolongo
  • 1,395
  • 2
  • 9
  • 17