Question about WITH ORDINALITY and unnest

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Question about WITH ORDINALITY and unnest

Mike Martin
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
Reply | Threaded
Open this post in threaded view
|

Re: Question about WITH ORDINALITY and unnest

David G Johnston
On Saturday, January 23, 2021, Mike Martin <[hidden email]> wrote:

Is there any way to make the ordinality ordering to be over the entire record set rather than per array

No
 

(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)

Yep, this is what I often do as well.

David J.
 
Reply | Threaded
Open this post in threaded view
|

Re: Question about WITH ORDINALITY and unnest

Shaozhong SHI
The most difficult part is unequal numbers of elements in arrays.  For instance, not well structured, delimited addresses.

Has anyone got a nice way to deal with unequal numbers of elements in arrays?

Regards,

David

On Sat, 23 Jan 2021 at 16:14, David G. Johnston <[hidden email]> wrote:
On Saturday, January 23, 2021, Mike Martin <[hidden email]> wrote:

Is there any way to make the ordinality ordering to be over the entire record set rather than per array

No
 

(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)

Yep, this is what I often do as well.

David J.
 
Reply | Threaded
Open this post in threaded view
|

Re: Question about WITH ORDINALITY and unnest

Tom Lane-2
Shaozhong SHI <[hidden email]> writes:
> Has anyone got a nice way to deal with unequal numbers of elements in
> arrays?

This might or might not be what you want, but:

db=# select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b);
 a |  b  
---+-----
 1 | foo
 2 | bar
   | baz
(3 rows)

                        regards, tom lane