Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s9tPQ-005pJh-Ow for pgsql-general@arkaria.postgresql.org; Wed, 22 May 2024 21:22:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s9tPQ-003w20-Ic for pgsql-general@arkaria.postgresql.org; Wed, 22 May 2024 21:22:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s9tPQ-003w1r-3C for pgsql-general@lists.postgresql.org; Wed, 22 May 2024 21:22:04 +0000 Received: from mail-vs1-xe31.google.com ([2607:f8b0:4864:20::e31]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s9tPL-001Vuh-73 for pgsql-general@lists.postgresql.org; Wed, 22 May 2024 21:22:02 +0000 Received: by mail-vs1-xe31.google.com with SMTP id ada2fe7eead31-47eee2a2a87so448447137.0 for ; Wed, 22 May 2024 14:21:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1716412918; x=1717017718; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=HQMbpTgN6kLcjqQXYbqvjvllwUuvvP0qsVifTSFZW7M=; b=iNeLLtUmmxIQdQiEBsAp2/rtGsS9C22+hu74KwVkBD5WQU71i+fvUtzegtfkqM+9ns UXa4tt/mJpu2yC5qFVsVbAYQKiAevwqnXU/zyO+nQgEWKZyAsnjB4xQPH5ZSaekkDuXr pMrgqS4oXyLSd3qPKxNkCWLH1UbPCMBppDLVJu5I8FSTcJvq/k8cpAMpaObCTj04wUB6 cpWYQOqtG1Td8Q/7I1loI62LAsdkaIJQyC/DhwgOIRysj/U+BTjEKdPnbFzL5M6d7vey 3+Rn0PjdoR/pEH9tQzlZct4mpZ6zCb9YGK68l37vMyuhViSLep0Dj/1xY35SrvO304x5 o8qQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716412918; x=1717017718; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=HQMbpTgN6kLcjqQXYbqvjvllwUuvvP0qsVifTSFZW7M=; b=DsHNWXq8tw1znYHeEZ7TamnSWsZV6gPwmvbqzt9RGbBRz/ld/xEPbSjszmSjOVtocW jQFP5y1l8IFJaiCM8hsgHnPVAjmUgreNIvM1BruJtzmQF8kPBucezd/V0MQOfwjZWidH gbTuRqjlhpJ4JMWkt1gJRsmmZE5cZneIEcANghaWgw1niP8ccpkccgRWwNc2Zn7Hlblu HqqjP66KTpmwpr3X1ioaG9Fqmj+aSlUx1ebWg+uM6SnA5Z01v57IQztxocJi3C/Cf9x3 mnd8cTBT+21Ke+fwTX57J6oKK24gzJVa/DbtVZErtvD1qeoltpY9ab5GXo0bsB8FQjLU Q9Hg== X-Forwarded-Encrypted: i=1; AJvYcCWs8DSFrHytO5W7HcXNqSElacytVePR1G2vVq6FJaqglBNkxLowhpxs4D7sudUHkZL+F805A6RuuFichYNtNbVhO1Ahvub0y3iK9ibQtfeoLous X-Gm-Message-State: AOJu0YyeC1s6oZRi2LPngl1QwQui2EQQbPGRI11aeCBMkAIQTRWUd6Iu 5/kQ4HFTSAAx4EsgT3Luf+8J3c1TfYna4JGdNbNlE93ldPpyj6+/+Neo6gngwIZuboiZxMstUCB 6Ya53U8pS+tRUfRqHovMZD5P/Nmk= X-Google-Smtp-Source: AGHT+IEsf9swVpaK4ocZU/B28coXuw0RWROdJIX6kB3niHA1a6NXBEEEZq1lLKLexcVcsqD3fzKqUIP8JRcVQ68RzKg= X-Received: by 2002:a05:6102:290f:b0:47e:f14d:ac49 with SMTP id ada2fe7eead31-4890a2c6daemr3471053137.29.1716412918362; Wed, 22 May 2024 14:21:58 -0700 (PDT) MIME-Version: 1.0 References: <98fd5198e1fe4e162f1166fd0575844de8bb7714.camel@loonybin.net> In-Reply-To: From: Ken Tanzer Date: Wed, 22 May 2024 14:21:22 -0700 Message-ID: Subject: Re: Finding "most recent" using daterange To: Alban Hertroys Cc: Rob Foehl , pgsql-general Content-Type: multipart/alternative; boundary="000000000000497af50619118611" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000497af50619118611 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, May 22, 2024 at 11:07=E2=80=AFAM Alban Hertroys wrote: > > Sounds like a good candidate for using EXISTS to prove that no more recen= t > value exists for a given id: > > SELECT e.id, e.value, e.dates > FROM example AS e > WHERE NOT EXISTS ( > SELECT 1 > FROM example AS i > WHERE i.id =3D e.id > AND (coalesce(upper(i.dates), 'infinity') > > coalesce(upper(e.dates), 'infinity') > OR (coalesce(upper(i.dates), 'infinity') =3D > coalesce(upper(e.dates), 'infinity') > AND coalesce(lower(i.dates), '-infinity') > > coalesce(lower(e.dates), '-infinity')) > ) > ); > > Not sure if I'm missing something, but what about just using DISTINCT? SELECT DISTINCT ON (id) id,value,dates FROM example ORDER BY id,upper(dates) desc, lower(dates) desc; 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, Ken --=20 AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ * *https://demo.agency-software.org/client * ken.tanzer@agency-software.org (253) 245-3801 Subscribe to the mailing list to learn more about AGENCY or follow the discussion. --000000000000497af50619118611 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, May 22, 2024 at 11:07=E2=80=AFAM = Alban Hertroys <= haramrae@gmail.com> wrote:

Sounds like a good candidate for using EXISTS to prove that no more recent = value exists for a given id:

SELECT e.id, e.value, e.dates
FROM example AS e
WHERE NOT EXISTS (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT 1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM example AS i
=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE i.id =3D e.id
=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND (coalesce(upper(i.dates), 'infinity'= ;) > coalesce(upper(e.dates), 'infinity')
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 OR (coalesce(upper(= i.dates), 'infinity') =3D coalesce(upper(e.dates), 'infinity= 9;)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND coalesce(lower(= i.dates), '-infinity') > coalesce(lower(e.dates), '-infinity= '))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 )
);


Not sure if I'm missing something, but what abou= t=C2=A0just using DISTINCT?

SELECT DISTINCT ON (id) id,value,dates FROM example ORD= ER BY id,upper(dates) desc, lower(dates) desc;

=C2=A0id | = value | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0dates =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0
----+-------+-------------------------
=C2=A0 1 | b =C2=A0 = =C2=A0 | [2010-01-01,)
=C2=A0 2 | d =C2=A0 =C2=A0 | [2010-01-01,2021-01-= 01)
=C2=A0 3 | g =C2=A0 =C2=A0 | [2013-01-01,)
=C2=A0 4 | j =C2=A0 = =C2=A0 | [2010-01-01,2015-01-01)
(4 rows)


Cheers,
Ke= n

= --
<= div>
AGENCY Software =C2= =A0
A Free Software data system
By and for non-pro= fits
(253)= 245-3801

learn more about AGENCY or
<= div>follow the discussion.
--000000000000497af50619118611--