Get group maxima from combined strings

Question

I have a table with a column code containing multiple pieces of data like this:

001/2017/TT/000001
001/2017/TT/000002
001/2017/TN/000003
001/2017/TN/000001
001/2017/TN/000002
001/2016/TT/000001
001/2016/TT/000002
001/2016/TT/000001
002/2016/TT/000002

There are 4 items in 001/2016/TT/000001: 001, 2016, TT and 000001.
How can I extract the max for every group formed by the first 3 items? The result I want is this:

001/2017/TT/000003
001/2017/TN/000002
001/2016/TT/000002
002/2016/TT/000002

Edit

The subfield separator is /, and the length of subfields can vary.
I use PostgreSQL 9.3.

Is the subfield length constant? Or just the separator '/'? – Erwin Brandstetter Feb 23 '17 at 09:30 — Erwin Brandstetter, Feb 23 '17 at 09:30
no, just a separator `/` @ErwinBrandstetter – Youcef LAIDANI Feb 23 '17 at 09:31 — Youcef LAIDANI, Feb 23 '17 at 09:31

score 1 · Answer 1 · answered Feb 23 '17 at 09:14

1

Use the LEFT and RIGHT functions.

SELECT MAX(RIGHT(code,6)) AS MAX_CODE
FROM yourtable
GROUP BY LEFT(code,12)

answered Feb 23 '17 at 09:14

Matt

14,906
27
99
149

score 1 · Accepted Answer · edited May 23 '17 at 11:46

Obviously, you should normalize the table and split the combined string into 4 columns with proper data type. The function split_part() is the tool of choice if the separator '/' is constant in your string and the length of can vary.

CREATE TABLE tbl_better AS 
SELECT split_part(code, '/', 1)::int AS col_1  -- better names?
     , split_part(code, '/', 2)::int AS col_2
     , split_part(code, '/', 3)      AS col_3  -- text?
     , split_part(code, '/', 4)::int AS col_4
FROM   tbl_bad
ORDER  BY 1,2,3,4  -- optionally cluster data.

Then the task is trivial:

SELECT col_1, col_2, col_3, max(col_4) AS max_nr
FROM   tbl_better
GROUP  BY 1, 2, 3;

Split comma separated column data into additional columns

Of course, you can do it on the fly, too. For varying subfield length you could use substring() with a regular expression like this:

SELECT max(substring(code, '([^/]*)$')) AS max_nr
FROM   tbl_bad
GROUP  BY substring(code, '^(.*)/');

Related (with basic explanation for regexp pattern):

Filter strings with regex before casting to numeric

Or to get only the complete string as result:

SELECT DISTINCT ON (substring(code, '^(.*)/'))
       code
FROM   tbl_bad
ORDER  BY substring(code, '^(.*)/'), code DESC;

About DISTINCT ON:

Select first row in each GROUP BY group?

Be aware that data items cast to a suitable type may behave differently from their string representation. The max of 900001 and 1000001 is 900001 for text and 1000001 for integer ...

Thank you @Erwin Brandstetter i think this should help me, i need something, i want to select all the code `001/2017/TT/000003` to insert it into another table, your query gives me just the last part `000003` — Youcef LAIDANI, Feb 23 '17 at 09:59
@YCF_L: I added another solution. Now with the missing `DESC` in `ORDER BY` to get the max. — Erwin Brandstetter, Feb 23 '17 at 10:14
hoho Thank you @Erwin Brandstetter, the second solution work soo good, some clarification the second one work until there columns not contain the same separator and this is more good, thank youu :) — Youcef LAIDANI, Feb 23 '17 at 10:38

score 0 · Answer 3 · answered Feb 23 '17 at 10:28

check this out, possible helpfull

select 
distinct on (tab[4],tab[2]) tab[4],tab[3],tab[2],tab[1]
from
(
    select
    string_to_array(exe.x,'/') as tab,
    exe.x
    from
    (
        select
        unnest
            (
            array
            ['001/2017/TT/000001',
            '001/2017/TT/000002',
            '001/2017/TN/000003',
            '001/2017/TN/000001',
            '001/2017/TN/000002',
            '001/2016/TT/000001',
            '001/2016/TT/000002',
            '001/2016/TT/000001',
            '002/2016/TT/000002']
            ) as x
    ) exe
) exe2
order by tab[4] desc,tab[2] desc,tab[3] desc;

Get group maxima from combined strings

3 Answers3