select from grouped data
От | Roger Mason |
---|---|
Тема | select from grouped data |
Дата | |
Msg-id | y65sfyxdbq4.fsf@mun.ca обсуждение исходный текст |
Ответы |
Re: select from grouped data
Re: select from grouped data |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: