CODEWITHSUNDEEP
postgresqlpostgresql-9.1postgresql-9.2
Shoaib Ijaz
Shoaib Ijaz

Reputation: 5587

postgresql-sort array by words in each elements

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?

Upvotes: 3

Views: 5561

Answers (2)

Flabiento
Flabiento

Reputation: 11

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

Upvotes: 1

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions