Postgresql – Postgres get rows where a field stopps growing

postgresql

I have a table which has the following structure

id SERIAL PRIMARY KEY,
device_id INT REFERENCES(another_table),
uptime TIMESTAMP

Every device is pinged every N minutes.
The application logic and db is (Sadly) made so that for each ping instead of an update to one row there is a new INSERT.

How can I get the rows where the next row's timestamp is smaller than this row's in the context where they are grouped by their id's in ascending order (you basically start from the smallest one and finish with the biggest one) ?

Can it be done with some sort of group by or should it be written in pl/SQL or something along those lines ?

Best Answer

Use the window function lead() in a subquery:

SELECT id, device_id, uptime
FROM  (
   SELECT *, lead(uptime) OVER (PARTITION BY device_id ORDER BY id) AS next_uptime
   FROM   tbl
   ) sub
WHERE  next_uptime < uptime

BTW, "where a field stops growing" would translate to <=, but "where the next row's timestamp is smaller" requires <.

Related Question