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 <t...@sss.pgh.pa.us> wrote:
> "Markhof, Ingolf" <ingolf.mark...@de.verizon.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
