dedup

Use the `dedup` stage to eliminate redundant records from your query's result set, ensuring that each returned record or combination of field values is unique.

## Syntax

```sql
dedup <field1>[,<field2>, ...] by asc | desc <field>
```

## Parameters

| Name | Type | Required | Description |
| --------------------- | ---------------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- |
| `field1`, `field2`... | string, integer, float | Yes | One or more fields used to identify duplicate records. If multiple fields are specified, the combination of values must be unique. |
| `by` | clause | No | Determines which of the duplicate records is kept based on the value of a specified field. You must specify `asc` (ascending) or `desc` (descending). |

## Returns

The `dedup` stage returns a result set where the specified field (or combination of fields) contains only unique values.

## Usage notes

* When records contain duplicate values (or duplicate sets of values) for the specified fields, `dedup` ensures only one such record is retained.
* The `by` clause is optional but crucial: it determines _which_ of the duplicate records is kept. If no `by` clause is provided, the record returned is arbitrary among the duplicates.
* The `dedup` stage can only be used with fields that contain numbers or strings.
* Note that the `dedup` stage does not preserve the sort order established by preceding stages. If a specific order is required for the final output after deduplication, a `sort` stage should be placed _after_ `dedup`.
* While highly useful, the `dedup` stage can be resource-intensive, especially when applied to very large datasets like `xdr_data`. This is because `dedup` operations often involve a self-join, which can significantly impact query performance and cost.
* **Best practice**: Avoid using `dedup` unless it is explicitly necessary for your analytical objective. When you do use it, ensure that any preceding `filter` or `fields` stages have already minimized the data being processed to optimize performance.

## Examples

### Example 1: Basic dedup on a single field

**Goal**: Remove duplicate records based on the specified single field (`is_successful`), retaining an arbitrary record from the duplicates.

**XQL code**:

```sql
dataset = sample_xql_raw
| fields event_id, is_successful 
| dedup is_successful
```

**Explanation**: This query removes duplicate records based on the `is_successful` field. Since no `by` clause is provided, the specific `event_id` associated with each `true` or `false` result is arbitrary.

**Output**:

| EVENT\_ID | IS\_SUCCESSFUL |
| --------- | -------------- |
| 101 | true |
| 102 | false |

### Example 2: Dedup by earliest time

**Goal**: Return unique records based on a field, explicitly keeping the record with the earliest timestamp.

**XQL code**:

```sql
dataset = sample_xql_raw
| fields event_id, _time, is_successful
| dedup is_successful by asc _time
```

**Explanation**: This query deduplicates based on `is_successful`. By using `by asc _time`, it ensures that for any set of duplicate records (for example, all successful events), the record with the earliest `_time` value is kept.

**Output**:

| EVENT\_ID | \_TIME | IS\_SUCCESSFUL |
| --------- | ----------------------- | -------------- |
| 101 | 2023-10-26 10:00:00 UTC | true |
| 102 | 2023-10-26 10:05:30 UTC | false |

### Example 3: Dedup by latest time

**Goal**: Return unique records based on a field, explicitly keeping the record with the latest timestamp.

**XQL code**:

```sql
dataset = sample_xql_raw
| fields event_id, _time, is_successful
| dedup is_successful by desc _time
```

**Explanation**: This query deduplicates based on `is_successful`. By using `by desc _time`, it ensures that for any set of duplicate records, the record with the latest `_time` value is kept.

**Output**:

| EVENT\_ID | \_TIME | IS\_SUCCESSFUL |
| --------- | ----------------------- | -------------- |
| 110 | 2023-10-26 11:00:10 UTC | true |
| 109 | 2023-10-26 10:55:55 UTC | false |

### Example 4: Dedup on multiple fields

**Goal**: Remove records where the _combination_ of values across multiple specified fields is identical.

**XQL code**:

```sql
dataset = sample_xql_raw
| fields event_id, event_description, is_successful
| dedup event_description, is_successful
```

**Explanation**: This query removes records where the combination of `event_description` and `is_successful` is duplicated. In the sample data, since each `event_description` is unique, the combinations are unique, and all records are returned. If duplicates existed, only one per combination would be kept.

**Output**:

| EVENT\_ID | EVENT\_DESCRIPTION | IS\_SUCCESSFUL |
| --------- | -------------------------------- | -------------- |
| 101 | "User login successful" | true |
| 102 | "File access attempt" | false |
| 103 | "Network connection established" | true |
| 104 | "System heartbeat" | true |
| 105 | "Data transformation" | true |
| 106 | "Unauthorized access detected" | false |
| 107 | "Cloud resource modification" | true |
| 108 | "Software update initiated" | true |
| 109 | "API request throttled" | false |
| 110 | "Database backup completed" | true |

### Example 5: Dedup on multiple fields with a `by` clause

**Goal**: Deduplicate based on multiple fields and use a specific ordering criterion to determine which record to keep.

**XQL code**:

```sql
dataset = sample_xql_raw
| fields event_id, event_description, is_successful, duration_seconds
| dedup event_description, is_successful by asc duration_seconds
```

**Explanation**: This query deduplicates based on the unique combination of `event_description` and `is_successful`. The `by asc duration_seconds` clause specifies that if duplicate combinations were found, the record with the smallest `duration_seconds` would be retained.

**Output**:

| EVENT\_ID | EVENT\_DESCRIPTION | IS\_SUCCESSFUL | DURATION\_SECONDS |
| --------- | -------------------------------- | -------------- | ----------------- |
| 101 | "User login successful" | true | 1.5 |
| 102 | "File access attempt" | false | 0.8 |
| 103 | "Network connection established" | true | 10.2 |
| 104 | "System heartbeat" | true | 0.1 |
| 105 | "Data transformation" | true | 5.0 |
| 106 | "Unauthorized access detected" | false | 2.1 |
| 107 | "Cloud resource modification" | true | 7.8 |
| 108 | "Software update initiated" | true | 15.3 |
| 109 | "API request throttled" | false | 0.05 |
| 110 | "Database backup completed" | true | 60.0 |

## Related articles

* **Stages**: [`fields`](fields), [`filter`](filter), [`sort`](sort)