Subject: select from grouped data

From: Roger Mason <[email-masked]>
Date: Wed, 25 Aug 2021 08:37:15 -0230

Hello,

I have written this function to process some multiline text data into a
table with both text & numeric columns:

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.jid,
    results. "timestamp",
    cast(
      CASE WHEN split_part(a.teo, ' ', 2) = '' THEN
        '0'
      ELSE
        split_part(a.teo, ' ', 2)
      END AS integer) AS scf,
    cast(
      CASE WHEN split_part(a.teo, ' ', 3) = '' THEN
        '0.0'
      ELSE
        split_part(a.teo, ' ', 3)
      END AS double precision) AS energy
  FROM
    results,
    a
  WHERE
    results.jid = a.jid
  GROUP BY
    results.jid,
    results. "timestamp",
    a.teo
  --HAVING
    -- scf = max(scf)
    ORDER BY
         timestamp ASC,
         scf DESC
)
SELECT
  *
FROM
  b;

$$
LANGUAGE sql;

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.

I appreciate any help,
Thanks,
Roger

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 25 Aug 2021 06:19:39 -0700

>    jid     |    timestamp    | scf |     energy
SELECT DISTINCT ON (jid) jid, timestamp, scf, energy [...] ORDER BY jid,
scf DESC

David J.

---

From: Roger Mason <[email-masked]>
Date: Wed, 25 Aug 2021 11:29:10 -0230

> SELECT DISTINCT ON (jid) jid, timestamp, scf, energy [...] ORDER BY jid,

Got it.

Many thanks,
Roger

---

From: Stephen Frost <[email-masked]>
Date: Wed, 25 Aug 2021 16:42:56 -0400

Greetings,

> SELECT DISTINCT ON (jid) jid, timestamp, scf, energy [...] ORDER BY jid,

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.  Using a LATERAL join
also would allow you to have multiple rows (top-N) if you wanted.  You'd
do that using:

WITH jids AS (SELECT jid FROM results GROUP BY jid)
SELECT jids.jid, t.ts, t.scf, t.energy
FROM jids CROSS JOIN LATERAL
 (SELECT ts, scf, energy FROM results
  WHERE results.jid = jids.jid
  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.  Your jids sure
look like they should just be numbers instead of text too.  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.

Thanks,

Stephen

---

From: James Keener <[email-masked]>
Date: Wed, 25 Aug 2021 17:37:42 -0400

I don't know that "generally better" is the correct description. There are a lot of things not covered by the SQL standard and it would be crazy not to make use of them, e.g. insert on conflict and distinct on, especially when they make the query more readable and more correct. Worrying about sql portability is almost always time not well spent and an invitation to do things not as well as they can be.

Jim

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

---

From: Stephen Frost <[email-masked]>
Date: Wed, 25 Aug 2021 18:03:56 -0400

Greetings,

We generally prefer to reply in-line on these threads, as I’ve done below.
Thanks.

On Wed, Aug 25, 2021 at 17:37 James Keener <[email-masked]> wrote:

> I don't know that "generally better" is the correct description. There are
> a lot of things not covered by the SQL standard and it would be crazy not
> to make use of them, e.g. insert on conflict and distinct on, especially
> when they make the query more readable and more correct. Worrying about sql
> portability is almost always time not well spent and an invitation to do
> things not as well as they can be.
>

It’s generally better as it follows the SQL standard and is more flexible
as it allows you to get top-N, as I said. DISTINCT ON has always been a
hack and its use should be discouraged at this point.

When an UPSERT capability exists in the actual standard then it’s use
should be promoted over other hacks that various vendors have implemented
also.

Thanks again,

Stephen

---

From: Roger Mason <[email-masked]>
Date: Sat, 28 Aug 2021 10:38:41 -0230

> should be promoted over other hacks that various vendors have implemented

OK, thanks, I've filed this discussion away for future reference.
Meanwhile I'm having trouble getting this function to work as a
trigger.  I'll start another thread on that.

Thanks again.

Roger