How to get lead value of a specific case in Postgresql?

I have a table like below

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  group_id INT,
  code VARCHAR,
  created_date timestamptz
);

Sample values

INSERT INTO events (group_id, code, created_date) VALUES 
(1, '0001', clock_timestamp()),
(1, '000A', clock_timestamp()),
(1, '0002', clock_timestamp()),
(2, '000A', clock_timestamp()),
(2, '0003', clock_timestamp()),
(2, '0004', clock_timestamp()),
(3, '0001', clock_timestamp()),
(3, '000A', clock_timestamp()),
(4, '0001', clock_timestamp()),
(5, '000A', clock_timestamp()),
(5, '0002', clock_timestamp()),
(5, '000A', clock_timestamp()),
(5, '0006', clock_timestamp());

I have a query

SELECT 
 DISTINCT ON (group_id) group_id, 
 last_value(code) over w_last AS last_code, 
 last_value(created_date) over w_last AS last_created_date
FROM events
WINDOW
 w_last AS (PARTITION BY group_id ORDER BY created_date ROWS BETWEEN unbounded preceding AND unbounded following);

Query Result

| group_id | last_code | last_created_date        |
| -------- | --------- | ------------------------ |
| 1        | 0002      | 2020-11-18T09:25:53.443Z |
| 2        | 0004      | 2020-11-18T09:25:53.443Z |
| 3        | 000A      | 2020-11-18T09:25:53.443Z |
| 4        | 0001      | 2020-11-18T09:25:53.443Z |
| 5        | 0006      | 2020-11-18T09:25:53.443Z |

I'm trying to get one additional field to show event after last '000A' event

 Eg. for group_id 1 event code after 000A is 0002
     for group_id 2 event code after 000A is 0003
     for group_id 3 event code after 000A is NULL
     for group_id 4 event code after 000A is NULL
     for group_id 5 event code after 000A is 0006 (from last)

Expected Output

| group_id | last_code | after_last_000A | last_created_date        |
| -------- | --------- | --------------- | ------------------------ |
| 1        | 0002      |     0002        | 2020-11-18T09:25:53.443Z |
| 2        | 0004      |     0003        | 2020-11-18T09:25:53.443Z |
| 3        | 000A      |     NULL        | 2020-11-18T09:25:53.443Z |
| 4        | 0001      |     NULL        | 2020-11-18T09:25:53.443Z |
| 5        | 0006      |     0006        | 2020-11-18T09:25:53.443Z |

Same in DB Fiddle



Read more here: https://stackoverflow.com/questions/64890442/how-to-get-lead-value-of-a-specific-case-in-postgresql

Content Attribution

This content was originally published by bkmagnetron at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: