CODEWITHSUNDEEP
postgresqlsql-order-bydistinctaggregaterecursive-query
Rodrigo
Rodrigo

Reputation: 5119

Preserve the order of distinct inside string_agg

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.

Upvotes: 5

Views: 6000

Answers (2)

tbussmann
tbussmann

Reputation: 643

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

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.

Upvotes: 5

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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

Upvotes: 2

Related Questions