SQL: Sorting within the sorting

[Former Member]

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!

[Former Member]

I tried and it works for me like this

SELECT  CITY_ENT.CITY_NAME, STRING_AGG( CITY_ENT.ENTERPRISE_NAME,',')

FROM (

SELECT CITY_NAME, ENTERPRISE_NAME FROM "DK"."CITY_TEST"

LEFT JOIN "DK"."ENTERPRISE_TEST" ON "CITY_TEST".CITY_ID = "ENTERPRISE_TEST".CITY_ID

ORDER BY CITY_NAME, ENTERPRISE_NAME DESC ) AS CITY_ENT

GROUP BY  CITY_ENT.CITY_NAME ;

MAybe I got wrong something.

Anyways I have to say Thank You for asking question in a perfect way...with DDL with table inserts, with Your tried select and result You expect.  Example of Hw to do it, because with my daily Job theres like 5 min window to try to answer interesting question..

Anyway Thx.

EDIT: Even better would be if I said which version of Hana I use ( 1.00.093.00.1424770727 ), and which is Yours

I see now Lars has answered, so I would go with his answer.

Have a Nice Day

[Former Member]

Another thing,

I like Your naming convention, seems fully ISO 11179 compliant.

Celko would be proud

Ok, to be Dubravko I have to say something bad: I don't like your original query

[lbreddemann]

Using a subquery with ordering was also my first try.

Unfortunately I have seen cases, where the optimizer decided to transform the statement so that the inner query wouldn't feed the data sorted into the string_agg function.

If it works however, then it's way more flexible than creating a procedure.

And you're right: the question was really well prepared and deserved the thumbs up!

- Lars

[lbreddemann]

Sorting the list (or making it unique) in string_agg  is not (yet) supported.

You can work out a workaround like this:

drop procedure list_users_sort;

create procedure list_users_sort (OUT user_sort TABLE (user_group NVARCHAR(20), user_name NVARCHAR(256)))
language sqlscript
reads sql data with result view list_users_sorted
as 
begin
  user_sort = select  case 
  when left (user_name, 4) = '_SYS' THEN 'SYSTEM_USER'
  when left (user_name, 3) = 'SYS' THEN 'SYSTEM_USER'
  else
  'CUSTOM_USER'
  end as user_group , user_name 
  from users
  order by user_group, user_name;
end;

select user_group, string_agg( user_name, ', ') from 
 list_users_sorted
group by user_group;

USER_GROUP	STRING_AGG(USER_NAME,', ')
CUSTOM_USER	ADMIN, DEVDUDE, PUBLICJOE
SYSTEM_USER	SYS, SYSTEM, _SYS_AFL, _SYS_EPM, _SYS_REPO, _SYS_STATISTICS

- Lars

[sergey_zharko]

Lars, thanks for the answer!

Unfortunately, the following statement clears the first sorting

select user_group, string_agg( user_name, ', ')

from list_users_sorted 

group by user_group

order by 1;

[lbreddemann]

Does it work when you remove your ORDER BY 1?

As mentioned before this is a work-around as string_agg currently doesn't support sorting directly.

- Lars

[Former Member]

Starting with SPS 10 it is now possible to specify ORDER BY for STRING_AGG. Example:

SELECT
  X,
  STRING_AGG(Y, ',' ORDER BY Y) Y
FROM
( SELECT '1000' X, 'C' Y FROM DUMMY UNION ALL
  SELECT '1000' X, 'A' Y FROM DUMMY UNION ALL
  SELECT '1000' X, 'B' Y FROM DUMMY UNION ALL
  SELECT '2000' X, 'C' Y FROM DUMMY
)
GROUP BY
  X
ORDER BY
  X