There is string array
ARRAY['CAT','CAT DOG CAT','DOG Cat']
Now i want to sort this array on count of words in each element. I have tried but cannot get any success.
I want this output
ARRAY['CAT DOG CAT','DOG CAT','Cat']
How i can do this?
2 Answers
This does feel rather clumsy, but I can't think of a simpler solution right now:
with val (col) as (
values (ARRAY['CAT','CAT DOG CAT','DOG Cat'])
), word_list as (
select unnest(col) as pc
from val
), wc as (
select array_length(string_to_array(pc, ' '),1) as word_count, pc
from word_list
)
select array_agg(pc order by word_count desc)
from wc;
Comments
I came up with an alternative:
select array_agg(a)
from (
select unnest(array['CAT','CAT DOG CAT','DOG Cat']) as a
order by a) s
'Cat'cat come from in the output. There is no such element in the input.