---
title: ES|QL DEDUP command
description: 
url: https://www.elastic.co/elastic/docs-builder/docs/3895/reference/query-languages/esql/commands/dedup
products:
  - Elasticsearch
---

# ES|QL DEDUP command
<applies-to>
  - Elastic Cloud Serverless: Preview
  - Elastic Stack: Planned
</applies-to>

`DEDUP` removes duplicate rows from a result set, keeping only one row per unique combination of values across all columns.

## Syntax

```esql
DEDUP
```


## Description

`DEDUP` takes no arguments. It compares all columns currently in scope and discards any row that is an exact duplicate of a previously seen row. Null values are treated as equal for the purpose of deduplication.
`DEDUP` is equivalent to `LIMIT 1 BY <all columns>`.

### When to use `LIMIT ... BY` instead

`DEDUP` always keeps exactly one row per unique combination of *all* columns in
scope. Use [`LIMIT ... BY`](https://www.elastic.co/elastic/docs-builder/docs/3895/reference/query-languages/esql/commands/limit)
directly for the following two cases:
- **Keeping more than one copy of each duplicate.** To retain up to `N` rows per
  unique combination instead of just one, use `LIMIT N BY <all columns>`. Note
  that `LIMIT ... BY` does not support wildcards, so you must list every column
  explicitly:
  ```esql
  FROM employees
  | WHERE emp_no IN (10001, 10003, 10007, 10008, 10010)
  | KEEP languages
  | LIMIT 2 BY languages
  | SORT languages
  ```


  | languages:integer |
  |-------------------|
  | 2                 |
  | 2                 |
  | 4                 |
  | 4                 |
- **Deduplicating on a subset of the columns.** To treat rows as duplicates
  based on only some of the columns, while still returning the remaining
  columns, list just those columns in the `BY` clause. Precede it with a
  [`SORT`](https://www.elastic.co/elastic/docs-builder/docs/3895/reference/query-languages/esql/commands/sort) to control which
  row is kept for each group:
  ```esql
  FROM employees
  | WHERE emp_no IN (10001, 10002, 10003, 10005, 10006)
  | SORT last_name
  | LIMIT 1 BY gender
  | KEEP first_name, last_name, gender
  ```


  | first_name:keyword | last_name:keyword | gender:keyword |
  |--------------------|-------------------|----------------|
  | Parto              | Bamford           | M              |
  | Anneke             | Preusig           | F              |


## Limitations

`DEDUP` cannot be used when any column in scope has one of the following types: `aggregate_metric_double`, counter types (`counter_long`, `counter_integer`, `counter_double`), or `date_range`. Attempting to do so results in a validation error.
Full-text search functions (such as `MATCH` or `KQL`) cannot appear after `DEDUP` in the same pipeline.

## Examples

Remove duplicate values from a single column:
```esql
FROM employees
| WHERE emp_no IN (10001, 10002, 10003, 10005, 10006)
| KEEP gender
| DEDUP
| SORT gender
```


| gender:keyword |
|----------------|
| F              |
| M              |

Remove rows that are duplicates across multiple columns:
```esql
FROM employees
| WHERE emp_no IN (10001, 10002, 10010, 10011)
| KEEP gender, languages
| DEDUP
| SORT gender NULLS LAST, languages
```


| gender:keyword | languages:integer |
|----------------|-------------------|
| F              | 5                 |
| M              | 2                 |
| null           | 4                 |
| null           | 5                 |