By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
date datetime,
message varchar(20),
rnk_id int
);
insert into mytable values
('2022-12-19 10:48:51', 'mess1', 8),
('2022-12-19 10:57:13', 'mess2', 8),
('2022-12-19 10:57:23', 'mess3', 8),
('2022-12-19 10:57:49', 'mess4', 8),
('2022-12-19 10:57:58', 'mess5', 8),
('2022-12-19 10:58:07', 'mess6', 8),
('2022-12-19 11:00:36', 'mess7', 8),
('2022-12-19 11:17:55', 'mess1', 5),
('2022-12-19 11:18:02', 'mess2', 5),
('2022-12-19 11:20:08', 'mess3', 5),
('2022-12-19 11:20:19', 'mess4', 5),
('2022-12-19 11:20:37', 'mess5', 5),
('2022-12-19 11:20:40', 'mess6', 5),
('2022-12-19 11:22:12', 'mess7', 5);
14 rows affected
with cte as (
select t.*,
first_value(case when message = 'mess1' then date end)
over (partition by rnk_id order by date) as mess1,
lead(date)
over (partition by rnk_id order by date) as mess2,
lead(date,2)
over (partition by rnk_id order by date) as mess3
from mytable t
)
select date, message, rnk_id, mess1,
first_value(mess2) over (partition by rnk_id order by date) as mess2,
first_value(mess3) over (partition by rnk_id order by date) as mess3
from cte
order by rnk_id desc
| date | message | rnk_id | mess1 | mess2 | mess3 |
|---|---|---|---|---|---|
| 2022-12-19 10:48:51.000 | mess1 | 8 | 2022-12-19 10:48:51.000 | 2022-12-19 10:57:13.000 | 2022-12-19 10:57:23.000 |
| 2022-12-19 10:57:13.000 | mess2 | 8 | 2022-12-19 10:48:51.000 | 2022-12-19 10:57:13.000 | 2022-12-19 10:57:23.000 |
| 2022-12-19 10:57:23.000 | mess3 | 8 | 2022-12-19 10:48:51.000 | 2022-12-19 10:57:13.000 | 2022-12-19 10:57:23.000 |
| 2022-12-19 10:57:49.000 | mess4 | 8 | 2022-12-19 10:48:51.000 | 2022-12-19 10:57:13.000 | 2022-12-19 10:57:23.000 |
| 2022-12-19 10:57:58.000 | mess5 | 8 | 2022-12-19 10:48:51.000 | 2022-12-19 10:57:13.000 | 2022-12-19 10:57:23.000 |
| 2022-12-19 10:58:07.000 | mess6 | 8 | 2022-12-19 10:48:51.000 | 2022-12-19 10:57:13.000 | 2022-12-19 10:57:23.000 |
| 2022-12-19 11:00:36.000 | mess7 | 8 | 2022-12-19 10:48:51.000 | 2022-12-19 10:57:13.000 | 2022-12-19 10:57:23.000 |
| 2022-12-19 11:17:55.000 | mess1 | 5 | 2022-12-19 11:17:55.000 | 2022-12-19 11:18:02.000 | 2022-12-19 11:20:08.000 |
| 2022-12-19 11:18:02.000 | mess2 | 5 | 2022-12-19 11:17:55.000 | 2022-12-19 11:18:02.000 | 2022-12-19 11:20:08.000 |
| 2022-12-19 11:20:08.000 | mess3 | 5 | 2022-12-19 11:17:55.000 | 2022-12-19 11:18:02.000 | 2022-12-19 11:20:08.000 |
| 2022-12-19 11:20:19.000 | mess4 | 5 | 2022-12-19 11:17:55.000 | 2022-12-19 11:18:02.000 | 2022-12-19 11:20:08.000 |
| 2022-12-19 11:20:37.000 | mess5 | 5 | 2022-12-19 11:17:55.000 | 2022-12-19 11:18:02.000 | 2022-12-19 11:20:08.000 |
| 2022-12-19 11:20:40.000 | mess6 | 5 | 2022-12-19 11:17:55.000 | 2022-12-19 11:18:02.000 | 2022-12-19 11:20:08.000 |
| 2022-12-19 11:22:12.000 | mess7 | 5 | 2022-12-19 11:17:55.000 | 2022-12-19 11:18:02.000 | 2022-12-19 11:20:08.000 |