create an array sorted by a field

data1233 — Fri, 02 Feb 2024 20:21:25 GMT

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                                                     |
| ]                                                            |
+--------------------------------------------------------------+

Re: create an array sorted by a field

feiyun0112 — Sat, 03 Feb 2024 02:50:28 GMT

%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

topic Re: create an array sorted by a field in Data Engineering

create an array sorted by a field

Re: create an array sorted by a field