Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-14-2024 11:22 AM
I had a similar situation where I was trying to order the days of the week from Monday to Sunday. I saw solutions that use Python but was wanting to do it all in SQL.
My original attempt was to use:
CONCAT_WS(',', COLLECT_LIST(DISTINCT t.LOAD_ORIG_DAY_BL))
But it did not order correctly and would not take an ORDER BY clause like the LIST_AGG in ORACLE. Here is the replacement that works perfectly - The collect_list wrapped in a custom array_sort function...
CONCAT_WS(',', ARRAY_SORT(COLLECT_LIST(DISTINCT t.LOAD_ORIG_DAY_BL),
(left, right) -> CASE
WHEN left = 'MON' AND right IN ('TUE','WED','THU','FRI','SAT','SUN') THEN -1
WHEN left = 'TUE' AND right IN ('WED','THU','FRI','SAT','SUN') THEN -1
WHEN left = 'WED' AND right IN ('THU','FRI','SAT','SUN') THEN -1
WHEN left = 'THU' AND right IN ('FRI','SAT','SUN') THEN -1
WHEN left = 'FRI' AND right IN ('SAT','SUN') THEN -1
WHEN left = 'SAT' AND right IN ('SUN') THEN -1
ELSE 1
END))
