How to order a string aggregation based on another column in postgresql?

Question

So basically I have 2 polygon tables, tableA that I want to update with the aggregated strings from tableB. tableB has 2 columns

TableB is structure something like

viable | fruit_id
yes    | banana1
no     | apple2
maybe  | watermelon1
no     | peach3

My update query looks like:

update TableA a set
    fruitids = (select string_agg(fruit_id, ', ' order by fruit_id)
                from tableB b st_contains(b.geom, a.geom))

But this will just return me the fruit ids in alphabetical order. How can I make it so that it will list the viable ones first? In this case my intended output would be:

banana1, watermelon1, apple2, peach3

You shouldn't be storing comma separated values to begin with. Normalize your model and create a proper many-to-many relationship between tablea and tableb — user330315
– user330315, Commented Jul 11, 2018 at 8:54
@a_horse_with_no_name I appreciate the data concerns but If I create a many to many relationship with these 2 tables then I would easily have over 100 million rows. Essentially I need people to look at the viable record on the beginning of the column and in case it's no good then they'll need to go through the 2-8 other strings inside the record. That's why I need the SQL execution wisdom from you or another SQL Jedi master — Luffydude
– Luffydude, Commented Jul 11, 2018 at 9:01

user330315 · Accepted Answer · 2018-07-11 09:06:13Z

3

you can use conditional sorting:

select string_agg(fruit_id, ', ' order by 
                                   case viable
                                      when 'yes' then 1 
                                      when 'maybe' then 2
                                      else 3 
                                   end, 
                                   fruit_id)
from tableB b st_contains(b.geom, a.geom)

answered Jul 11, 2018 at 9:06

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to order a string aggregation based on another column in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related