Search Postgresql Archives

Re: [E] Re: string_agg distinct order by

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux