create an array sorted by a field

data1233 · ‎02-02-2024

How do i create an array from a field while applying sorting?

how do I do this in data brick since databricks does not support order by in array_agg? same is possible in Snowflake and(Array agg) or Redshift(listagg).

SELECT ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY timestamp ASC)
  FROM orders 
+--------------------------------------------------------------+
| ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BYtimestamp ASC) |
|--------------------------------------------------------------|
| [                                                            |
|   41445,                                                     |
|   55937,                                                     |
|   67781,                                                     |
|   80550,                                                     |
|   95808,                                                     |
|   101700,                                                    |
|   103136                                                     |
| ]                                                            |
+--------------------------------------------------------------+

feiyun0112 · ‎02-02-2024

%sql
SELECT array_sort(array_agg(col)
  ,(left, right) -> CASE WHEN left < right THEN -1
    WHEN left > right THEN 1 ELSE 0 END) arr_col FROM VALUES (3), (2), (1) AS tab(col);

https://docs.databricks.com/en/sql/language-manual/functions/array_sort.html