Re: [E] Re: string_agg distinct order by

Tom, I see your point. Which is valid considering there could be any value
in col_y for some value in col_x. But in my case, col_y is a a function of
col_x, i.e. two rows with the same value in row_x will have the same value
in row_y as well.

Consider, you need to store some length values. Like this:

create table items (
id text,
len integer,
unit text
);

insert into items values (1,1,'mm');
insert into items values (2,5,'mm');
insert into items values (3,5,'mm');
insert into items values (4,1,'cm');
insert into items values (5,1,'cm');
insert into items values (6,1,'m');
insert into items values (7,1,'m');
insert into items values (7,2,'m');
insert into items values (8,2,'m');
insert into items values (9,5,'m');

With the view...

create view vu_items as
select
id,
len || unit as descr,
len*case unit when 'mm' then 1 when 'cm' then 10 when 'm' then 1000 end
as len_mm
from items;

...I now want to have a list of all distinct descr ordered by length. But...

select
string_agg(descr,',' order by len_mm)
from vu_items;

...creates a list with duplicates, only:

1mm,5mm,5mm,1cm,1cm,1m,1m,2m,2m,5m

And...

select
string_agg(distinct descr,',' order by descr)
from vu_items;

...gives a list of distinct values, but in the wrong order:

1cm,1m,1mm,2m,5m,5mm

My solution to this (now) is:

select
regexp_replace(
string_agg(descr,',' order by len_mm),
'([^,]+)(,\1)?($|,)',
'\1\3',
'g'
)
from vu_items;

Thx again for your hint in the regexp_replacy issue in my other post...

Regards,
Ingolf

On Thu, Aug 19, 2021 at 6:30 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Markhof, Ingolf" <ingolf(dot)markhof(at)de(dot)verizon(dot)com> writes:
> > I am looking for something like
> > string_agg(distinct col_x order by col_y)
>
> > Unfortunately, you can either have the distinct, but then the order by
> > needs to be identical to what's aggregated, or you can have the order be
> > determined by another column. Not both...
>
> The reason for that restriction is that the case you propose is
> ill-defined. If we combine rows with the same col_x, which row's
> value of col_y is to be used to sort the merged row? I think once
> you answer that question, a suitable query will suggest itself.
>
> regards, tom lane
>

======================================================================

Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio