> ## Documentation Index > Fetch the complete documentation index at: https://docs.e6data.com/llms.txt > Use this file to discover all available pages before exploring further. # COLLECT_LIST > Collects values into an array; non-DISTINCT calls skip NULLs, while grouped DISTINCT calls can retain one NULL.
Aggregate ยท Window
**Aliases:** [`array_agg`](/query/sql/functions/aggregate/array_agg) Returns a non-NULL array. Grouped DISTINCT calls keep one NULL when present; ordinary calls skip NULL values. ## Syntax A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group. ### Aggregate ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} collect_list([DISTINCT] value) ``` Grouped DISTINCT calls keep one NULL when present. Ordinary calls skip NULL values. WITHIN GROUP is accepted only when ordering by the collected value, but does not sort a grouped result. ### Window ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} collect_list(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame]) ``` Window calls currently ignore DISTINCT: duplicate values remain, and NULL values are skipped. For an ordered window covering all rows in the group: ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} collect_list(value) WITHIN GROUP (ORDER BY value ASC NULLS LAST) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ``` This window form applies the ordering. WITHIN GROUP must order by the collected value, not a separate column. ## Arguments ### Aggregate and window | Name | Type | Required | Description | | - | - | - | - | | value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT | Yes | Input values to collect. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). | ## Returns * Type: ARRAY with the SQL type of value as its element type * NULL behavior: Returns a non-NULL array. Grouped DISTINCT calls keep one NULL when present; ordinary calls skip NULL values. * Empty input: Returns \[] on empty input. All-NULL non-DISTINCT input returns \[]; all-NULL grouped DISTINCT input returns \[NULL]. * Shape: One-dimensional array; grouped DISTINCT calls retain one of each value. * Ordering: Grouped aggregation returns elements in an unspecified order, even with WITHIN GROUP. Use ARRAY\_SORT for a sorted grouped result. In a full-group window call, WITHIN GROUP ordering is applied, as shown in Examples. ## Properties | Property | Value | | - | - | | Deterministic | Partial (Element order is unspecified; sort the result for a stable displayed order.) | | Supports DISTINCT | Partial (Removes duplicates in grouped aggregation; window calls still include duplicates.) | | Supports window usage | Partial (Sliding ROWS frames are supported for the window syntax shown above.) | | Supports window frames | Partial (ROWS frames without DISTINCT are supported. RANGE offsets are not supported.) | | Exact result | Yes | ## Usage notes * Use ARRAY\_SORT(array\_agg(value)) for sorted non-NULL values. * COLLECT\_SET keeps at most one NULL; ARRAY\_UNIQUE\_AGG removes NULL. * INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear. * In grouped aggregation, DISTINCT removes duplicate values and retains one NULL. Without DISTINCT, NULL values are skipped. ## Examples ### Basic example ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH t("value") AS (VALUES (1),(1),(CAST(NULL AS INTEGER))) SELECT collect_list("value") AS result FROM t; ``` | result | | - | | \[1,1] | ### Ordinary and DISTINCT NULL policies * ARRAY\_SORT displays ascending values with NULL last. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH sample(id, v) AS (VALUES (1, CAST(NULL AS INTEGER)), (2, 1), (3, 1)) SELECT array_sort(collect_list(v)) AS ordinary, array_sort(collect_list(DISTINCT v)) AS distinct_values FROM sample; ``` | ordinary | distinct\_values | | - | - | | \[1,1] | \[1,null] | ### Sliding collection skips NULL ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH sample(id, v) AS (VALUES (1, CAST(NULL AS INTEGER)), (2, 1), (3, 1)) SELECT id, array_sort(collect_list(v) OVER (ORDER BY id ASC NULLS LAST ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) AS result FROM sample ORDER BY id ASC NULLS LAST; ``` | id | result | | - | - | | 1 | \[] | | 2 | \[1] | | 3 | \[1,1] | ### Sort the collected values ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH sample(v) AS (VALUES (3), (CAST(NULL AS INTEGER)), (1), (1)) SELECT array_sort(collect_list(v)) AS result FROM sample; ``` | result | | - | | \[1,1,3] | A full-group window applies WITHIN GROUP ordering and keeps each input row: ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH t(v) AS (VALUES (30), (10), (20)) SELECT collect_list(v) WITHIN GROUP (ORDER BY v ASC NULLS LAST) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS result FROM t; ``` | result | | - | | \[10,20,30] | | \[10,20,30] | | \[10,20,30] | ## Limitations * In grouped aggregation, WITHIN GROUP ordering is currently ignored, with or without DISTINCT. ARRAY\_SORT gives an ascending grouped result with NULL last. * WITHIN GROUP accepts only one ordering expression, and it must be the collected value. A separate sort column is rejected. Full-group window calls apply this ordering. * Sliding ROWS frames are supported for the window syntax shown above. * RANGE frames with numeric or time offsets are not supported. * DISTINCT in a window call currently has no effect: duplicate rows still contribute to the result. Use grouped DISTINCT aggregation when duplicate removal is required. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.