public inbox for [email protected]  

help / color / mirror / Atom feed

Re: Finding "most recent" using daterange
4+ messages / 2 participants
[nested] [flat]

* Re: Finding "most recent" using daterange
@ 2024-05-22 14:14 Greg Sabino Mullane <[email protected]>
  2024-05-22 15:13 ` Re: Finding "most recent" using daterange Isaac Morland <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Greg Sabino Mullane @ 2024-05-22 14:14 UTC (permalink / raw)
  To: Rob Foehl <[email protected]>; +Cc: [email protected]

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


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Finding "most recent" using daterange
  2024-05-22 14:14 Re: Finding "most recent" using daterange Greg Sabino Mullane <[email protected]>
@ 2024-05-22 15:13 ` Isaac Morland <[email protected]>
  2024-05-22 15:35   ` Re: Finding "most recent" using daterange Greg Sabino Mullane <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Isaac Morland @ 2024-05-22 15:13 UTC (permalink / raw)
  To: Greg Sabino Mullane <[email protected]>; +Cc: Rob Foehl <[email protected]>; [email protected]

On Wed, 22 May 2024 at 10:15, Greg Sabino Mullane <[email protected]>
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.


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Finding "most recent" using daterange
  2024-05-22 14:14 Re: Finding "most recent" using daterange Greg Sabino Mullane <[email protected]>
  2024-05-22 15:13 ` Re: Finding "most recent" using daterange Isaac Morland <[email protected]>
@ 2024-05-22 15:35   ` Greg Sabino Mullane <[email protected]>
  2024-05-22 16:13     ` Re: Finding "most recent" using daterange Isaac Morland <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Greg Sabino Mullane @ 2024-05-22 15:35 UTC (permalink / raw)
  To: Isaac Morland <[email protected]>; +Cc: Rob Foehl <[email protected]>; [email protected]

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

Cheers,
Greg


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Finding "most recent" using daterange
  2024-05-22 14:14 Re: Finding "most recent" using daterange Greg Sabino Mullane <[email protected]>
  2024-05-22 15:13 ` Re: Finding "most recent" using daterange Isaac Morland <[email protected]>
  2024-05-22 15:35   ` Re: Finding "most recent" using daterange Greg Sabino Mullane <[email protected]>
@ 2024-05-22 16:13     ` Isaac Morland <[email protected]>
  0 siblings, 0 replies; 4+ messages in thread

From: Isaac Morland @ 2024-05-22 16:13 UTC (permalink / raw)
  To: Greg Sabino Mullane <[email protected]>; +Cc: Rob Foehl <[email protected]>; [email protected]

On Wed, 22 May 2024 at 11:36, Greg Sabino Mullane <[email protected]>
wrote:

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


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2024-05-22 16:13 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-22 14:14 Re: Finding "most recent" using daterange Greg Sabino Mullane <[email protected]>
2024-05-22 15:13 ` Isaac Morland <[email protected]>
2024-05-22 15:35   ` Greg Sabino Mullane <[email protected]>
2024-05-22 16:13     ` Isaac Morland <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox