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 ) id 1mJ0am-0001kO-CT for pgsql-novice@arkaria.postgresql.org; Wed, 25 Aug 2021 21:37:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mJ0al-0002sT-By for pgsql-novice@arkaria.postgresql.org; Wed, 25 Aug 2021 21:37:51 +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 ) id 1mJ0ak-0002sH-TY for pgsql-novice@lists.postgresql.org; Wed, 25 Aug 2021 21:37:51 +0000 Received: from mail-qk1-x733.google.com ([2607:f8b0:4864:20::733]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mJ0ah-0002Jy-5K for pgsql-novice@lists.postgresql.org; Wed, 25 Aug 2021 21:37:49 +0000 Received: by mail-qk1-x733.google.com with SMTP id t190so1054369qke.7 for ; Wed, 25 Aug 2021 14:37:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jimkeener-com.20150623.gappssmtp.com; s=20150623; h=date:user-agent:in-reply-to:references:mime-version :content-transfer-encoding:subject:to:cc:from:message-id; bh=FZxf41jThjbrCqQ9S9EXT8cyIpDexI9hqQfbbsNzY3Q=; b=0j8PeT26dNXfhp9mgOTr0hhCRLpGKdWkCp9ksNvkhrd3mazWcpIS2Jpl1T9swE41/6 ju0iGCX7vjSFQevOGdeVG9f1bgf8mIuGqimvgkukdE6ai53TcPYUYleTWuO+LnlTPRoq q5jAlgXF8q4/NUrH2AYSj0rL16ihOAL2QCn7tyU87q/UQ7HcH+XzupBau8Ipd5M+p9Cu Ji2j+cbmD7PenYC8wOCcANfC9qJMtIfFJTQtclyKeMJ0N9ZnknE4KxpxAZWwfB05bav1 Vv+PXVlJR1YxSvIUIg1GRTtyUYP9ZM6Pfk8m2PZp7YM5JD9TLm/YM5IHhH6e7DbWC9ai w3Gw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:user-agent:in-reply-to:references :mime-version:content-transfer-encoding:subject:to:cc:from :message-id; bh=FZxf41jThjbrCqQ9S9EXT8cyIpDexI9hqQfbbsNzY3Q=; b=siz0o6fMXjzRekgQqC/AlovhNeCQ2FmIXuDiq+utJWS9UTWuV91YrjXMy300spvlTa BH7d97HcjuOMtcNmSNGqt7KLzGQxSsVWiDK6nNk0JgnZVcqiJ8G0OTw8407WDxJF4hu8 vHYe7tx0way5I+0mJhWzW1v/VOdDc1rN2rebcELyg9JQkekh+NNuU4PkHit2VExG1QfK niKRPOxvjCXxKXcuPQCkSIkxbEbYrHCRp3e098VD1V9A4nGM7N9iuSBujmxOhIuf0b8q /hTLkHAiwxvo32cV8tJnWdnJ03yR8HORBhkCA0oC1ExzgoQSqaXG8WUYKwvohOzVZ+ca J6mA== X-Gm-Message-State: AOAM532Y/yOWz7A0b0N7dte79rl0cYqcTdsndhR7dbypaqKMPTBbcFrM 4B/CK3Csl+B2tn7hnIs+sK4op30FiPoqnQ== X-Google-Smtp-Source: ABdhPJzeUCCT6clCyRr3KUWNxKOglDwLnMu6S1tgf6S1mlkiDHm3Z0oQhN4R22v+855Z80tcTWlA4g== X-Received: by 2002:a05:620a:c05:: with SMTP id l5mr676229qki.17.1629927465972; Wed, 25 Aug 2021 14:37:45 -0700 (PDT) Received: from Matilda.home (pool-72-77-44-213.pitbpa.fios.verizon.net. [72.77.44.213]) by smtp.gmail.com with ESMTPSA id f3sm651777qti.65.2021.08.25.14.37.45 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 25 Aug 2021 14:37:45 -0700 (PDT) Date: Wed, 25 Aug 2021 17:37:42 -0400 User-Agent: K-9 Mail for Android In-Reply-To: <20210825204256.GX17906@tamriel.snowman.net> References: <20210825204256.GX17906@tamriel.snowman.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----6GYUTP8GG59OKQJJ26BVB8ZWG8MRB3" Content-Transfer-Encoding: 7bit Subject: Re: select from grouped data To: pgsql-novice@lists.postgresql.org,Stephen Frost ,Roger Mason ,"David G. Johnston" CC: pgsql-novice From: James Keener Message-ID: <77511C44-068E-4047-A955-9C6C08BC1942@jimkeener.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------6GYUTP8GG59OKQJJ26BVB8ZWG8MRB3 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable I don't know that "generally better" is the correct description=2E There ar= e a lot of things not covered by the SQL standard and it would be crazy not= to make use of them, e=2Eg=2E insert on conflict and distinct on, especial= ly when they make the query more readable and more correct=2E Worrying abou= t sql portability is almost always time not well spent and an invitation to= do things not as well as they can be=2E Jim On August 25, 2021 4:42:56 PM EDT, Stephen Frost wr= ote: >Greetings, > >* Roger Mason (rmason@mun=2Eca) wrote: >> I have written this function to process some multiline text data into >a >> table with both text & numeric columns: >>=20 >> CREATE OR REPLACE FUNCTION get_final_energy () >> RETURNS TABLE ( >> jid text, >> "timestamp" text, >> scf integer, >> energy double precision >> ) >> AS $$ >> WITH a AS ( >> SELECT >> jid, >> regexp_split_to_table(totenergy_out, '\n') AS teo >> FROM >> results >> ), >> b AS ( >> SELECT >> results=2Ejid, >> results=2E "timestamp", >> cast( >> CASE WHEN split_part(a=2Eteo, ' ', 2) =3D '' THEN >> '0' >> ELSE >> split_part(a=2Eteo, ' ', 2) >> END AS integer) AS scf, >> cast( >> CASE WHEN split_part(a=2Eteo, ' ', 3) =3D '' THEN >> '0=2E0' >> ELSE >> split_part(a=2Eteo, ' ', 3) >> END AS double precision) AS energy >> FROM >> results, >> a >> WHERE >> results=2Ejid =3D a=2Ejid >> GROUP BY >> results=2Ejid, >> results=2E "timestamp", >> a=2Eteo >> --HAVING >> -- scf =3D max(scf) >> ORDER BY >> timestamp ASC, >> scf DESC >> ) >> SELECT >> * >> FROM >> b; >>=20 >> $$ >> LANGUAGE sql; >>=20 >> The output looks like: >>=20 >> jid | timestamp | scf | energy =20 >> ------------+-----------------+-----+---------------- >> 1250_1 | 20210805-114634 | 18 | -1316=2E43700819 >> 1250_1 | 20210805-114634 | 17 | -1316=2E43700825 >> 1250_1 | 20210805-114634 | 16 | -1316=2E4370097 >> 1250_1 | 20210805-114634 | 15 | -1316=2E43700991 >> 1250_1 | 20210805-114634 | 14 | -1316=2E43699775 >> 1250_1 | 20210805-114634 | 13 | -1316=2E43699117 >> 1250_1 | 20210805-114634 | 12 | -1316=2E43750771 >> 1250_1 | 20210805-114634 | 11 | -1316=2E43805358 >> 1250_1 | 20210805-114634 | 10 | -1316=2E43857192 >> 1250_1 | 20210805-114634 | 9 | -1316=2E43070942 >> 1251_1 | 20210806-062539 | 18 | -1316=2E43700819 >> 1251_1 | 20210806-062539 | 17 | -1316=2E43700826 >> =2E=2E=2E=2E >>=20 >> What I want is to get (for each group) the energy corresponding to >the >> maximum value of scf=2E > >* David G=2E Johnston (david=2Eg=2Ejohnston@gmail=2Ecom) wrote: >> > >> > >> > The output looks like: >> > >> > jid | timestamp | scf | energy >> > ------------+-----------------+-----+---------------- >> > 1250_1 | 20210805-114634 | 18 | -1316=2E43700819 >> > 1250_1 | 20210805-114634 | 17 | -1316=2E43700825 >> > 1250_1 | 20210805-114634 | 16 | -1316=2E4370097 >> > 1250_1 | 20210805-114634 | 15 | -1316=2E43700991 >> > 1250_1 | 20210805-114634 | 14 | -1316=2E43699775 >> > 1250_1 | 20210805-114634 | 13 | -1316=2E43699117 >> > 1250_1 | 20210805-114634 | 12 | -1316=2E43750771 >> > 1250_1 | 20210805-114634 | 11 | -1316=2E43805358 >> > 1250_1 | 20210805-114634 | 10 | -1316=2E43857192 >> > 1250_1 | 20210805-114634 | 9 | -1316=2E43070942 >> > 1251_1 | 20210806-062539 | 18 | -1316=2E43700819 >> > 1251_1 | 20210806-062539 | 17 | -1316=2E43700826 >> > =2E=2E=2E=2E >> > >> > What I want is to get (for each group) the energy corresponding to >the >> > maximum value of scf=2E >> > >> > >> SELECT DISTINCT ON (jid) jid, timestamp, scf, energy [=2E=2E=2E] ORDER = BY >jid, >> scf DESC > >While this works, it's generally better to use a LATERAL join as that's >part of the SQL standard while DISTINCT ON isn't=2E Using a LATERAL join >also would allow you to have multiple rows (top-N) if you wanted=2E=20 >You'd >do that using: > >WITH jids AS (SELECT jid FROM results GROUP BY jid) >SELECT jids=2Ejid, t=2Ets, t=2Escf, t=2Eenergy >FROM jids CROSS JOIN LATERAL > (SELECT ts, scf, energy FROM results > WHERE results=2Ejid =3D jids=2Ejid > ORDER BY scf DESC > LIMIT 1) AS t >; > >A couple of notes on this: don't name a column "timestamp" and when >it's >a timestamp, use the 'timestamptz' data type, not text=2E Your jids sure >look like they should just be numbers instead of text too=2E If you >already have a distinct set of jids somewhere (like in another table), >you could use that as the source table instead of the CTE that I'm >using >above=2E > >Thanks, > >Stephen --=20 Sent from my Android device with K-9 Mail=2E Please excuse my brevity=2E ------6GYUTP8GG59OKQJJ26BVB8ZWG8MRB3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable I don't know that "generally better" is the correc= t description=2E There are a lot of things not covered by the SQL standard = and it would be crazy not to make use of them, e=2Eg=2E insert on conflict = and distinct on, especially when they make the query more readable and more= correct=2E Worrying about sql portability is almost always time not well s= pent and an invitation to do things not as well as they can be=2E

Ji= m

On August 25, 2021 4:42:56 PM EDT, Step= hen Frost <sfrost@snowman=2Enet> wrote:
Greetings,

* Roger Mason (rmason@mun=2Eca) wr=
ote:
I have written thi=
s function to process some multiline text data into a
table with both te=
xt & numeric columns:

CREATE OR REPLACE FUNCTION get_final_energ=
y ()
  RETURNS TABLE (
    jid text,
    "timestamp" text,
    =
scf integer,
    energy double precision
  )
  AS $$
  WITH a A=
S (
    SELECT
      jid,
      regexp_split_to_table(totenergy_ou=
t, '\n') AS teo
    FROM
      results
),
b AS (
  SELECT    results=2Ejid,
    results=2E "timestamp",
    cast(
      CA=
SE WHEN split_part(a=2Eteo, ' ', 2) =3D '' THEN
        '0'
      ELS=
E
        split_part(a=2Eteo, ' ', 2)
      END AS integer) AS scf,    cast(
      CASE WHEN split_part(a=2Eteo, ' ', 3) =3D '' THEN
 =
       '0=2E0'
      ELSE
        split_part(a=2Eteo, ' ', 3)
    =
  END AS double precision) AS energy
  FROM
    results,
    a
=
  WHERE
    results=2Ejid =3D a=2Ejid
  GROUP BY
    results=2Ejid=
,
    results=2E "timestamp",
    a=2Eteo
  --HAVING
    -- scf=
 =3D max(scf)
    ORDER BY
         timestamp ASC,
         scf DE=
SC
)
SELECT
  *
FROM
  b;

$$
LANGUAGE sql;

=
The output looks like:

   jid     |    timestamp    | scf |     ener=
gy     
------------+-----------------+-----+----------------
 1250_1=
     | 20210805-114634 |  18 | -1316=2E43700819
 1250_1     | 20210805-1=
14634 |  17 | -1316=2E43700825
 1250_1     | 20210805-114634 |  16 |  -1=
316=2E4370097
 1250_1     | 20210805-114634 |  15 | -1316=2E43700991
=
 1250_1     | 20210805-114634 |  14 | -1316=2E43699775
 1250_1     | 202=
10805-114634 |  13 | -1316=2E43699117
 1250_1     | 20210805-114634 |  1=
2 | -1316=2E43750771
 1250_1     | 20210805-114634 |  11 | -1316=2E43805=
358
 1250_1     | 20210805-114634 |  10 | -1316=2E43857192
 1250_1   =
  | 20210805-114634 |   9 | -1316=2E43070942
 1251_1     | 20210806-0625=
39 |  18 | -1316=2E43700819
 1251_1     | 20210806-062539 |  17 | -1316=
=2E43700826
=2E=2E=2E=2E

What I want is to get (for each group) t=
he energy corresponding to the
maximum value of scf=2E
<=
br>* David G=2E Johnston (david=2Eg=2Ejohnston@gmail=2Ecom) wrote:


 The output looks like:

    jid     |    t=
imestamp    | scf |     energy
 ------------+-----------------+-----+---=
-------------
  1250_1     | 20210805-114634 |  18 | -1316=2E43700819  1250_1     | 20210805-114634 |  17 | -1316=2E43700825
  1250_1     | =
20210805-114634 |  16 |  -1316=2E4370097
  1250_1     | 20210805-114634 =
|  15 | -1316=2E43700991
  1250_1     | 20210805-114634 |  14 | -1316=2E=
43699775
  1250_1     | 20210805-114634 |  13 | -1316=2E43699117
  12=
50_1     | 20210805-114634 |  12 | -1316=2E43750771
  1250_1     | 20210=
805-114634 |  11 | -1316=2E43805358
  1250_1     | 20210805-114634 |  10=
 | -1316=2E43857192
  1250_1     | 20210805-114634 |   9 | -1316=2E43070=
942
  1251_1     | 20210806-062539 |  18 | -1316=2E43700819
  1251_1 =
    | 20210806-062539 |  17 | -1316=2E43700826
 =2E=2E=2E=2E

 Wha=
t I want is to get (for each group) the energy corresponding to the
 max=
imum value of scf=2E


SELECT DISTINCT ON (jid) jid, =
timestamp, scf, energy [=2E=2E=2E] ORDER BY jid,
scf DESC

While this works, it's generally better to use a LATERAL join as that=
's
part of the SQL standard while DISTINCT ON isn't=2E  Using a LATERAL =
join
also would allow you to have multiple rows (top-N) if you wanted=2E=
  You'd
do that using:

WITH jids AS (SELECT jid FROM results GROU=
P BY jid)
SELECT jids=2Ejid, t=2Ets, t=2Escf, t=2Eenergy
FROM jids CR=
OSS JOIN LATERAL
 (SELECT ts, scf, energy FROM results
  WHERE result=
s=2Ejid =3D jids=2Ejid
  ORDER BY scf DESC
  LIMIT 1) AS t
;
A couple of notes on this: don't name a column "timestamp" and when it's<=
br>a timestamp, use the 'timestamptz' data type, not text=2E  Your jids sur=
e
look like they should just be numbers instead of text too=2E  If youalready have a distinct set of jids somewhere (like in another table),you could use that as the source table instead of the CTE that I'm usingabove=2E

Thanks,

Stephen

--=
Sent from my Android device with K-9 Mail=2E Please excuse my brevity= =2E ------6GYUTP8GG59OKQJJ26BVB8ZWG8MRB3--