I have a table that look like:
| id | Value | Created |
|---|---|---|
| 1 | 'Apple' | 2021-12-25T20:15:00 |
| 2 | 'Blueberry' | 2021-12-25T20:45:00 |
| 3 | 'Cranberry' | 2021-12-25T21:30:00 |
| 4 | 'Durian' | 2022-01-01T20:15:00 |
| 5 | 'Elderbery' | 2022-01-01T20:30:00 |
I'm looking to write a query where I get the oldest document, with the caveat that if another row was created within an hour, it will return that row instead. For example, in the above I would like to return Cranberry. Initially pick Apple, but since Blueberry comes within an hour, move to that one, and since Cranberry comes within an hour of Blueberry, select Cranberry.
CodePudding user response：
You can get the difference with the next row (ordered by Created) using lead, then check for the first row for which the following row comes after a full hour:
select id, Value, Created from
(select id, Value, Created,
EXTRACT(EPOCH FROM lead(Created) over(order by Created) - Created)/3600 as diff
from table_name) t
where diff > 1 or diff is null
order by Created
limit 1