> ## 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_SET > Collects distinct values into an array, including at most one NULL.
Aggregate ยท Window
The result ARRAY is non-NULL. NULL input values are retained as one distinct NULL element; all-NULL nonempty input yields \[NULL]. ## 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_set([DISTINCT] value) ``` ### Window ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} collect_set(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame]) ``` * COLLECT\_SET always removes duplicates. For windows, use all rows in the group or a frame starting at its first row; sliding frames are not supported. * Use a frame that includes all rows in the group or starts at the first row (`UNBOUNDED PRECEDING`). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported. ## 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: The result ARRAY is non-NULL. NULL input values are retained as one distinct NULL element; all-NULL nonempty input yields \[NULL]. * Empty input: Returns \[] for an empty global aggregate or empty frame. An all-NULL nonempty group/frame returns \[NULL]. * Shape: One-dimensional array containing one element for each distinct input value, including at most one NULL. * Ordering: Element order is unspecified. WITHIN GROUP is accepted but currently does not apply its ordering; use ARRAY\_SORT on the result when sorted values are needed. ## Properties | Property | Value | | - | - | | Deterministic | Partial (Element order is unspecified; sort the result for a stable displayed order.) | | Supports DISTINCT | Partial (Duplicates are already removed.) | | Supports window usage | Partial (Use all rows in the group or start at its first row. Sliding frames and RANGE offsets are not supported.) | | Supports window frames | Partial (Use all rows in the group or start at its first row. Sliding frames and RANGE offsets are not supported.) | | Exact result | Yes | ## Usage notes * Duplicate elimination is inherent; no DISTINCT keyword is needed. * COLLECT\_SET retains a NULL element when any input is NULL. ARRAY\_UNIQUE\_AGG explicitly removes NULL instead. * To exclude NULL, filter the input with WHERE value IS NOT NULL before aggregation. * Use a frame that includes all rows in the group or starts at the first row (`UNBOUNDED PRECEDING`). Sliding frames that drop earlier rows are not supported. * 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. * Duplicate values are removed whether or not you write DISTINCT. Repeated NULL values become one NULL; DISTINCT does not remove that NULL. ## Examples ### Distinct values including NULL, sorted * ARRAY\_SORT orders ascending with NULL last. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH t(v) AS (VALUES (7),(7),(CAST(NULL AS INTEGER))) SELECT array_sort(collect_set(v)) AS result FROM t; ``` | result | | - | | \[7,null] | ### All NULL values ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH t(v) AS (VALUES (CAST(NULL AS INTEGER)),(CAST(NULL AS INTEGER))) SELECT array_sort(collect_set(v)) AS result FROM t; ``` | result | | - | | \[null] | ### Empty input ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH t(v) AS (VALUES (1)) SELECT collect_set(v) AS result FROM t WHERE FALSE; ``` | result | | - | | \[] | ### Filter NULL before collecting * WHERE removes NULL rows before aggregation. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} WITH t(v) AS (VALUES (7),(7),(CAST(NULL AS INTEGER))) SELECT array_sort(collect_set(v)) AS result FROM t WHERE v IS NOT NULL; ``` | result | | - | | \[7] | ### Distinct values across all rows in each group ```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_set(v) OVER ()) AS result FROM sample ORDER BY id ASC NULLS LAST; ``` | id | result | | - | - | | 1 | \[1,null] | | 2 | \[1,null] | | 3 | \[1,null] | ### Sort distinct values and NULL ```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_set(v)) AS result FROM sample; ``` | result | | - | | \[1,3,null] | ## Limitations * WITHIN GROUP ordering is currently ignored. Sort the result with ARRAY\_SORT for ascending values with NULL last. * Sliding frames that drop earlier rows and RANGE frames with numeric or time offsets are not supported. * IGNORE NULLS/RESPECT NULLS clauses are unsupported; filter the input to remove NULL. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.