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
);
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');
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;
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;
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;
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;
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@xxxxxxxxxxxxx> wrote:
"Markhof, Ingolf" <ingolf.markhof@xxxxxxxxxxxxxx> 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
