From: | Mike Martin <redtux1(at)gmail(dot)com> |
---|---|
To: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Question about WITH ORDINALITY and unnest |
Date: | 2021-01-23 13:17:09 |
Message-ID: | CAOwYNKahL2DCvqx7E_aiWkFE50aHuK3wBwPyb-2_u+P9ObRu_A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi
I have a strange case with ordinality and row number with multiple
rows/arrays.
The row numbering is per array, rather than for the whole set. ie
I have a two row data set with
row 1 {val1,val2,val3,val4,val5}
row 2 {vala,valb}
Then the
result of
SELECT DISTINCT
array_agg(uargs ORDER BY nr),
og.transref,fileid
FROM og,UNNEST (groupargs) WITH ORDINALITY uarg(uargs,nr)
GROUP By og.transref,fileid
(og is a cte with grouping of trasref and fileid)
is
{val1,val1,val2,val2,val3,val4,val5}
or without the grouping
val1,1
vala,1
val2,2
valb,2
val3,3
val4,4
val5,5
which really messes up the ordering of the array.
Is there any way to make the ordinality ordering to be over the entire
record set rather than per array
(the idea is to combine two arrays of varying dimensions into one, keeping
order)
I have got a working solution by adding a rowid and ordering the array by
eg: rowid+nr (where rowid is 100,200, big enough not to be in record set)
thanks
Mike
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-01-23 16:14:19 | Re: Question about WITH ORDINALITY and unnest |
Previous Message | Roberto Médola | 2021-01-20 12:58:22 | Re: Bring to dead tuples to alive |