How to sort values in group_concat()

Question

I tried to sort a group_concat list but keeping getting error. Below is what I've tried. If someone can please help.

eg.

customer order_id  status
ABC      1234      1
ABC      1235      5
DEF      1236      1
DEF      1237      5

Script used:

GROUP_CONCAT(CAST(status AS STRING), ', ') AS status

result:

 customer   status
 ABC        1, 5
 DEF        5, 1

but would like the result to return the following:

 customer   status
 ABC        1, 5
 DEF        1, 5

Tried the following script but keep getting error msg:

GROUP_CONCAT(CAST(status AS STRING), ', ' ORDER BY status) AS status_ordered,

D.lola · Accepted Answer · 2024-08-21 22:11:02Z

0

All you need is the order by, You can try this code example.

WITH SortedStatus AS (
    SELECT 
        customer, 
        SORT_ARRAY(COLLECT_LIST(CAST(status AS STRING))) AS sorted_status
    FROM 
        your_table_name
    GROUP BY 
        customer
)

SELECT 
    customer, 
    CONCAT_WS(', ', sorted_status) AS status_ordered
FROM 
    SortedStatus;

edited Aug 21, 2024 at 22:11

answered Aug 7, 2024 at 5:45

D.lola

2,3222 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user15676 Over a year ago

Hi, thank you for your help. but I get the following error: ParseException: Syntax error in line 554:undefined: ...(redemption_status AS CHAR) ORDER BY status ASC SEPARA... ^ Encountered: ) Expected: ( CAUSED BY: Exception: Syntax error

D.lola Over a year ago

what engine are you using?

user15676 Over a year ago

I'm using Impala Hive

D.lola Over a year ago

I forget about this problem , I have updated it hope it solved the issue @user15676

Collectives™ on Stack Overflow

How to sort values in group_concat()

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related