ordering string before concatenation in hive

Question

We have 3 columns in a table. Id, timestamp and comments. one Id can have several comments associated with it. We have a requirement to pick up top three comments based on timestamp in desc format, which we have done by using Rank() function. Next requirement was to concatenate those 3 top comments against an ID with pipe separation. We have used concat_ws to achieve this. However, we see that those comments are not getting concatenated with desc order of timestamp. They are getting concatenated randomly.

is there a way to do the concatenation is the same order for desc timestamp order without using a custom udf?

Answer 1

Use row_number() over (partition by id order by timestamp desc) and give a number to top three comments for each id. Then write query to concatenate comments based on rno you gave earlier.