Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-novice-owner+archive@lists.postgresql.org>)
	id 1mIsoy-0005lh-U0
	for pgsql-novice@arkaria.postgresql.org; Wed, 25 Aug 2021 13:20:01 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-novice-owner+archive@lists.postgresql.org>)
	id 1mIsox-0004vs-Ts
	for pgsql-novice@arkaria.postgresql.org; Wed, 25 Aug 2021 13:19:59 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <david.g.johnston@gmail.com>)
	id 1mIsox-0004vk-Ky
	for pgsql-novice@lists.postgresql.org; Wed, 25 Aug 2021 13:19:59 +0000
Received: from mail-pf1-x42d.google.com ([2607:f8b0:4864:20::42d])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128)
	(Exim 4.92)
	(envelope-from <david.g.johnston@gmail.com>)
	id 1mIsou-0003Da-U3
	for pgsql-novice@postgresql.org; Wed, 25 Aug 2021 13:19:58 +0000
Received: by mail-pf1-x42d.google.com with SMTP id a21so21236320pfh.5
        for <pgsql-novice@postgresql.org>;
 Wed, 25 Aug 2021 06:19:56 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20161025;
        h=mime-version:references:in-reply-to:from:date:message-id:subject:to
         :cc;
        bh=xbbScYff016e5BMb/jkcA7YIxMqpea+KuFr48/iwBiw=;
        b=uWSsLbOFO6Js6cVplfZN9SaDcvbUeBDmtAqI/90tUBbmSJQIjJekAE4omnVZL7mct7
         VqO/ppgFpdcL22U1Ma6FRlTD25KVRwEv5Nc0a1jYDl8xyMmC+QOLqfCaqmg3GbP9s54H
         CWWOjKdpe4Q6SNmm0WbrvDRkw0c763nJzJhvFU3bAn5r6hbiAIENSe4U//EO221hYw7Q
         PcgG1w6csg58bnYs3ri2o3LeM5sbyuBlZ6Gc4cdQ0BKjYBbmdj7PAgbpUF2GUro1tjZm
         XUckR34sXcqRmNCz6z47e56pKAmrMAy2+UlVex/I2vCcgWtgVwCwGWXOSMgE1b96N8bX
         bGug==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20161025;
        h=x-gm-message-state:mime-version:references:in-reply-to:from:date
         :message-id:subject:to:cc;
        bh=xbbScYff016e5BMb/jkcA7YIxMqpea+KuFr48/iwBiw=;
        b=cJl5gvsnkhumPwn+El4XEwNovPAryNsC1BMmNfPNliY1TQ4m0PFtKe8wRgFSDlARgs
         ArzL5C/KyCLlD4pH8JKf7GFDUUNPp7Rv/1vLexGrPfqTS1KgPaacd04Yom5S3oxM1Xrg
         Ta1vtd89qUe4OmF5ILj/LQYpZ1l2A4N6SydAccx0v3dwUwfeNgyiFlwNS+kEizM2ZL3l
         b+7yYi17pLWza3LWQm4Pjt+DZLxqVqQ73uTD0mEUpnONcWxJTGvfu5BhrvcrbF/2DS0l
         F4ffdefmtYneuFRmjKLPYpMHfGZ1QxarzGVLeL8uZfnorAilLCZ2uYm5uOEkZIVMhao7
         FFzA==
X-Gm-Message-State: AOAM533jU4e8LUwUgO7kq65HvdSU61yXgWxbRLLJcSc5G7AySMJQ0Oc3
	IMioy4eujEDyzCeghzAQw00rB3zvyy0wwYGvbS0=
X-Google-Smtp-Source: 
 ABdhPJy7OpLaTbmCpDmV2o9f+8xR7DQRtU8fWvxsXMa6M2OtCGFIZk0sYR0jvkSeaFaQijw8BIYtMPmrdv9lDSvvGlQ=
X-Received: by 2002:a05:6a00:1245:b029:30f:2098:fcf4 with SMTP id
 u5-20020a056a001245b029030f2098fcf4mr44532685pfi.66.1629897595637; Wed, 25
 Aug 2021 06:19:55 -0700 (PDT)
MIME-Version: 1.0
References: <y65sfyxdbq4.fsf@mun.ca>
In-Reply-To: <y65sfyxdbq4.fsf@mun.ca>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 25 Aug 2021 06:19:39 -0700
Message-ID: 
 <CAKFQuwavG_JG63EHyWxY_bM3s08Av1vu=XTzBTywLafUO0-htQ@mail.gmail.com>
Subject: Re: select from grouped data
To: Roger Mason <rmason@mun.ca>
Cc: pgsql-novice <pgsql-novice@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000035525205ca621b69"
List-Id: <pgsql-novice.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-novice@lists.postgresql.org>
List-Owner: <mailto:pgsql-novice-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-novice>
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwavG_JG63EHyWxY_bM3s08Av1vu%3DXTzBTywLafUO0-htQ%40mail.gmail.com>
Precedence: bulk

--00000000000035525205ca621b69
Content-Type: text/plain; charset="UTF-8"

>
>
> The output looks like:
>
>    jid     |    timestamp    | scf |     energy
> ------------+-----------------+-----+----------------
>  1250_1     | 20210805-114634 |  18 | -1316.43700819
>  1250_1     | 20210805-114634 |  17 | -1316.43700825
>  1250_1     | 20210805-114634 |  16 |  -1316.4370097
>  1250_1     | 20210805-114634 |  15 | -1316.43700991
>  1250_1     | 20210805-114634 |  14 | -1316.43699775
>  1250_1     | 20210805-114634 |  13 | -1316.43699117
>  1250_1     | 20210805-114634 |  12 | -1316.43750771
>  1250_1     | 20210805-114634 |  11 | -1316.43805358
>  1250_1     | 20210805-114634 |  10 | -1316.43857192
>  1250_1     | 20210805-114634 |   9 | -1316.43070942
>  1251_1     | 20210806-062539 |  18 | -1316.43700819
>  1251_1     | 20210806-062539 |  17 | -1316.43700826
> ....
>
> What I want is to get (for each group) the energy corresponding to the
> maximum value of scf.
>
>
SELECT DISTINCT ON (jid) jid, timestamp, scf, energy [...] ORDER BY jid,
scf DESC

David J.

--00000000000035525205ca621b69
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex"><br>
The output looks like:<br>
<br>
=C2=A0 =C2=A0jid=C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 timestamp=C2=A0 =C2=A0 |=
 scf |=C2=A0 =C2=A0 =C2=A0energy=C2=A0 =C2=A0 =C2=A0<br>
------------+-----------------+-----+----------------<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 18 | -1316.437008=
19<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 17 | -1316.437008=
25<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 16 |=C2=A0 -1316.=
4370097<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 15 | -1316.437009=
91<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 14 | -1316.436997=
75<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 13 | -1316.436991=
17<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 12 | -1316.437507=
71<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 11 | -1316.438053=
58<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 10 | -1316.438571=
92<br>
=C2=A01250_1=C2=A0 =C2=A0 =C2=A0| 20210805-114634 |=C2=A0 =C2=A09 | -1316.4=
3070942<br>
=C2=A01251_1=C2=A0 =C2=A0 =C2=A0| 20210806-062539 |=C2=A0 18 | -1316.437008=
19<br>
=C2=A01251_1=C2=A0 =C2=A0 =C2=A0| 20210806-062539 |=C2=A0 17 | -1316.437008=
26<br>
....<br>
<br>
What I want is to get (for each group) the energy corresponding to the<br>
maximum value of scf.<br><br></blockquote><div><br></div><div class=3D"gmai=
l_default" style=3D"font-family:arial,helvetica,sans-serif">SELECT DISTINCT=
 ON (jid) jid, timestamp, scf, energy [...] ORDER BY jid, scf DESC</div><di=
v class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">=
<br></div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica=
,sans-serif">David J.</div><div class=3D"gmail_default" style=3D"font-famil=
y:arial,helvetica,sans-serif"></div></div></div>

--00000000000035525205ca621b69--