On Wed, 22 May 2024 at 10:15, Greg Sabino Mullane <htamfids@gmail.com> wrote:
This is a good candidate for a window function. Also note that nulls already get sorted correctly by the DESC so no need to get 'infinity' involved, although you could write 'DESC NULLS FIRST' to be explicit about it.
with x as (select *, row_number() over (partition by id order by upper(dates) desc, lower(dates) desc) from example)
select id,value,dates from x where row_number = 1;
Don’t you need NULLS LAST for the lower bounds? There NULL means something closer to -infinity and should appear after the non-NULL values in a descending sort.
Actually it strikes me that this sorting issue could be a reason to avoid NULL bounds on ranges and prefer the use of +/-infinity if the underlying data type supports it.