Get next and previous rows ordered by column for a subset of table

Question

I have a subset of a table:

-- Subset
+---------------------------------+---------+-----------+--+
|date_time                        |animal_id|location_id|id|
+---------------------------------+---------+-----------+--+
|2023-04-17 11:11:11.000000 +00:00|43       |55         |11|
|2023-04-17 12:12:12.000000 +00:00|44       |57         |12|
+---------------------------------+---------+-----------+--+

The table contains next and previous rows for these rows for animal_id.

How to get those next and previous rows (ordered by date_time column) for each row in the subset?

-- Previous
+---------------------------------+---------+-----------+--+
|date_time                        |animal_id|location_id|id|
+---------------------------------+---------+-----------+--+
|2023-04-17 01:01:01.000000 +00:00|43       |45         |1 |
|2023-04-17 02:02:02.000000 +00:00|44       |47         |2 |
+---------------------------------+---------+-----------+--+

The query for last rows should return the rows before each row of the subset, ordered by date_time, for the animal_id of the row in the subset.

-- Next
+---------------------------------+---------+-----------+--+
|date_time                        |animal_id|location_id|id|
+---------------------------------+---------+-----------+--+
|2023-04-17 21:21:21.000000 +00:00|43       |65         |21|
|2023-04-17 22:22:22.000000 +00:00|44       |67         |22|
+---------------------------------+---------+-----------+--+

The same as previous, but rows after.

I found a simmilar question with an answer, but ultimately failed to integrate it for my use case as the method described there works for a single row (or so I think).

Table:

create table animals__locations (
    date_time   timestamptz default CURRENT_TIMESTAMP not null,
    animal_id   integer     not null,
    location_id integer     not null,
    id          serial      primary key
);

There is no unique constraint on (animal_id, date_time), yet. (Will create one!)
If there is no next/previous row, the row can be omitted (that is, no row in the resulting output)

DB schema

Subset query

PG version: 15.2

Probably using RANK OVER PARTITION. But what is the criteria for a row beloning to the subset? — Stringeater, Apr 17 '23 at 21:10
@Stringeater there is a fairly compicated query. Basically: the subset consists of last animal location in an area for an animal. I can post the query in the question body, if this would help — Shmookoff, Apr 17 '23 at 21:20
This would help: Exact table definition (`CREATE TABLE` statement), exact requirements, Postgres version. — Erwin Brandstetter, Apr 17 '23 at 21:27
@ErwinBrandstetter thanks for the answer! I have included the details in the question. — Shmookoff, Apr 17 '23 at 21:42
That clarifies: all columns in table `animals__locations` are `NOT NULL`. All other tables are irrelevant to this question. Put the definition of `animals__locations` into the question. What about possible duplicates on `(animal_id, date_time)`? What is the desired result if there is no next/previous row? — Erwin Brandstetter, Apr 17 '23 at 22:04
@ErwinBrandstetter I don't think `(animal_id, date_time)` can ever be duplicated in this scenareo. Will create an unique constraint. If there is no next/previous row, the row can be ommited (that is, no row in the resulting output) — Shmookoff, Apr 17 '23 at 22:12

Erwin Brandstetter · Accepted Answer · 2023-04-18T13:18:20.497

A join to a LATERAL subquery should do it.

SELECT prev.*
FROM   "subset" t
CROSS  JOIN LATERAL (
   SELECT *
   FROM   animals__locations t1
   WHERE  t1.animal_id = t.animal_id
   AND    t1.date_time < t.date_time
   ORDER  BY t1.date_time DESC
   LIMIT  1
   ) prev;

SELECT next.*
FROM   "subset" t
CROSS  JOIN LATERAL (
   SELECT *
   FROM   animals__locations t1
   WHERE  t1.animal_id = t.animal_id
   AND    t1.date_time > t.date_time
   ORDER  BY t1.date_time
   LIMIT  1
   ) next;

fiddle

There are various possible corner cases:

If there can be null values. --> All relevant columns NOT NULL.
If there can be identical (animal_id, date_time). --> Ruled out with new UNIQUE constraint.
If there is no next / previous row. --> No row. That's what my queries do.

You need an index on (animal_id, date_time) for this to be fast. Create a UNIQUE constraint on (animal_id, date_time), that rules out corner case 2. and provides that index implicitly.

Thank you very much! That's the best interaction I ever had on stackoverflow. Sorry you was forced to 'pull' information out of me haha. I'm fairly unexperienced in sql. Just found out about the idea of lateral joins, the links you provided are super helpful! — Shmookoff, Apr 17 '23 at 22:32

Get next and previous rows ordered by column for a subset of table

1 Answers1