Why is GROUP_CONCAT not ordering by the specified column in Impala?

Question

I'm trying to use the GROUP_CONCAT function in Impala and I want to order the results by one of the columns. Here's what I have so far:

SELECT column1, column2, GROUP_CONCAT(column3 ORDER BY column3 ASC) AS column3_concatenated
FROM table_name
GROUP BY column1, column2;

However, the results are not ordered by the specified column and are instead in a random order.

My expected result is that the GROUP_CONCAT function should order the results by the specified column.

Here's what I've tried so far:

• Tried using DESC instead of ASC
• Tried using a different column to order by

But none of these solutions have worked for me.

Can someone please help me figure out why GROUP_CONCAT is not ordering by the specified column and how I can modify my query to achieve this?

Thanks in advance!

Answer 1

order by doesn't work inside group_concat in Impala since the data is spread across nodes. So, use a sub_query with an order by and limit XXX (ensuring it's not lower than the number of rows you have) so that all of the data goes to one data node and then group_concat will work on that sub_query