how to use order by with collect_set() operation in hive

user_1576804 0
Viewed 12972

In Table 1, I have customer_id, item_id and item_rank (rank of item according to some sales). I want to collect a list of items for each customer_id and arrange them according to item_rank. 

Customer_id  item_id rank_item
  23            2      3
  23            2      3
  23            4      2
  25            5      1
  25            4      2

The output I expect is

Customer_id    item_list
  23             4,2
  25             5,4

The code I used was 

 SELECT
    customer_id,
    concat_ws(',',collect_list (string(item_id))) AS item_list
FROM
    table1
GROUP BY
    customer_id
ORDER BY
    item_rank
hive sql
2 Answers

SELECT customer_id, collect_set(item_id) AS item_list FROM table1 GROUP BY customer_id ORDER BY item_rank

NOTE : Using collect_list() gives you duplicates and collect_set() gives you unique values.

user_49576860
Related