SQL: Sorting within the sorting

47 Views

Hi everyone!

We have two simple tables:

CREATE COLUMN TABLE szharko.city_test (city_id VARCHAR(20) PRIMARY KEY, city_name VARCHAR(20));

insert into "SZHARKO"."CITY_TEST" values('1', 'Berlin');

insert into "SZHARKO"."CITY_TEST" values('2', 'Munich');

insert into "SZHARKO"."CITY_TEST" values('3', 'Minsk');

insert into "SZHARKO"."CITY_TEST" values('4', 'Gomel');

CREATE COLUMN TABLE szharko.enterprise_test (city_id VARCHAR(20), enterprise_name VARCHAR(20) PRIMARY KEY);

insert into "SZHARKO"."ENTERPRISE_TEST" values('1', 'Enterprise A');

insert into "SZHARKO"."ENTERPRISE_TEST" values('1', 'Enterprise B');

insert into "SZHARKO"."ENTERPRISE_TEST" values('2', 'Enterprise C');

insert into "SZHARKO"."ENTERPRISE_TEST" values('2', 'Enterprise D');

insert into "SZHARKO"."ENTERPRISE_TEST" values('3', 'Enterprise E');

insert into "SZHARKO"."ENTERPRISE_TEST" values('3', 'Enterprise F');

insert into "SZHARKO"."ENTERPRISE_TEST" values('4', 'Enterprise G');

insert into "SZHARKO"."ENTERPRISE_TEST" values('4', 'Enterprise H');

How can we make this table of the above two ones:

CITY_NAME ENTERPRISES Munich Enterprise D, Enterprise C Minsk Enterprise F, Enterprise E Gomel Enterprise H, Enterprise G Berlin Enterprise B, Enterprise A

I tried to make the following request:

select city_name, enterprises

from

(

select city.city_name, enterprise.enterprises

from

( select city_id, city_name from szharko.city_test ) city

left join

(

select city_id, string_agg(enterprise_name, ', ') as enterprises from

( select city_id, enterprise_name from szharko.enterprise_test order by enterprise_name desc )

group by city_id

) enterprise on city.city_id = enterprise.city_id

order by city_name desc

);

But it returns another table (enterprises sorted within rows in the wrong order, ASC instead DESC):

CITY_NAME ENTERPRISES Munich Enterprise C, Enterprise D Minsk Enterprise E, Enterprise F Gomel Enterprise G, Enterprise H Berlin Enterprise A, Enterprise B

Can I solve the original problem?

Thank you in advance!