Subject: Re: Finding "most recent" using daterange

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 22 May 2024 10:14:25 -0400

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;

 id | value |          dates
----+-------+-------------------------
  1 | b     | [2010-01-01,)
  2 | d     | [2010-01-01,2021-01-01)
  3 | g     | [2013-01-01,)
  4 | j     | [2010-01-01,2015-01-01)
(4 rows)

Cheers,
Greg

---

From: Isaac Morland <[email-masked]>
Date: Wed, 22 May 2024 11:13:12 -0400

>   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.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 22 May 2024 11:35:58 -0400

Oh, you are right - NULLS LAST, got my -infinity crossed with my infinity.
:)

Cheers,
Greg

---

From: Isaac Morland <[email-masked]>
Date: Wed, 22 May 2024 12:13:12 -0400

> Oh, you are right - NULLS LAST, got my -infinity crossed with my infinity.

NULLS LAST for lower bound, NULLS FIRST for upper bound.

The other way around if you were doing an ascending sort.