Preserve the order of distinct inside string_agg

Question

My SQL function:

with recursive locpais as (
    select l.id, l.nome, l.tipo tid, lp.pai
    from loc l
    left join locpai lp on lp.loc = l.id
    where l.id = 12554
    union
    select l.id, l.nome, l.tipo tid, lp.pai
    from loc l
    left join locpai lp on lp.loc = l.id
    join locpais p on (l.id = p.pai)
)
select * from locpais

gives me

12554 |     PARNA Pico da Neblina | 9 | 1564
12554 |     PARNA Pico da Neblina | 9 | 1547
 1547 |  São Gabriel da Cachoeira | 8 | 1400
 1564 | Santa Isabel do Rio Negro | 8 | 1400
 1400 |                 RIO NEGRO | 7 |  908
  908 |          NORTE AMAZONENSE | 6 |  234
  234 |                  Amazonas | 5 |  229
  229 |                     Norte | 4 |   30
   30 |                    Brasil | 3 |

which is a hierarchy of places. "PARNA" stands for "National Park", and this one covers two cities: São Gabriel da Cachoeira and Santa Isabel do Rio Negro. Thus it's appearing twice.

If I change the last line for

select string_agg(nome,', ') from locpais

I get

"PARNA Pico da Neblina, PARNA Pico da Neblina, São Gabriel da Cachoeira, Santa Isabel do Rio Negro, RIO NEGRO, NORTE AMAZONENSE, Amazonas, Norte, Brasil"

Which is almost fine, except for the double "PARNA Pico da Neblina". So I tried:

select string_agg(distinct nome, ', ') from locpais

but now I get

"Amazonas, Brasil, Norte, NORTE AMAZONENSE, PARNA Pico da Neblina, RIO NEGRO, Santa Isabel do Rio Negro, São Gabriel da Cachoeira"

Which is out of order. I'm trying to add an order by inside the string_agg, but couldn't make it work yet. The definition of the tables were given here.

http://stackoverflow.com/a/10470738/248343 ? `array_to_string` + `array_agg` instead of `string_agg` — OcuS, Mar 03 '16 at 14:48
@OcuS "select array_to_string(array_agg(distinct nome order by tid desc),', ') from locpais" but it gives "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list" which I'm not sure what it means. — Rodrigo, Mar 03 '16 at 15:03

score 4 · Answer 1 · edited May 23 '17 at 12:09

As you've found out, you cannot combine DISTINCT and ORDER BY if you don't order by the distinct expression first:

neither in aggregates:

If DISTINCT is specified in addition to an order_by_clause, then all the ORDER BY expressions must match regular arguments of the aggregate; that is, you cannot sort on an expression that is not included in the DISTINCT list.
nor in SELECT:

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

However could use something like

array_to_string(arry_uniq_stable(array_agg(nome ORDER BY tid DESC)), ', ')

with the help of a function arry_uniq_stable that removes duplicates in an array w/o altering it's order like I gave an example for in https://stackoverflow.com/a/42399297/5805552

Please take care to use an ORDER BY expression that actually gives you an deterministic result. With the example you have given, tid alone would be not enough, as there are duplicate values (8) with different nome.

score 2 · Accepted Answer · answered Mar 03 '16 at 15:54

2

select string_agg(nome,', ')
from (
    select distinct nome
    from locpais
    order by tid desc
) s

answered Mar 03 '16 at 15:54

Clodoaldo Neto

118,695
26
233
260

Preserve the order of distinct inside string_agg

2 Answers2

Linked