Search code examples
arrayshadoophiveconcatenationhiveql

How can I sort concat_ws(';',collect_set(name)) result in hive?

How can I sort result of this query alphabetically? 

select b6.S_Architect as S_Architect 
  from applications a
       left outer join
       (select id,concat_ws(';' , collect_set(name)) as S_Architect 
          from solution_architect
         group by parentcontentid
       ) b6 on a.id = b6.Id;

Expected result

Andy;Bravo;Cindy;David

Actual result

Cindy;Bravo;David;Andy
Solution

  • Use sort_array:

    select concat_ws('\073',sort_array(collect_set(name))) from (select stack(4,'Cindy','Bravo','David','Andy') as name) s;

    Returns:

    OK
Andy;Bravo;Cindy;David