# Getting Distinct Values
**URL:** https://discuss.elastic.co/t/getting-distinct-values/7024
**Category:** Elasticsearch
**Created:** [March 16, 2012, 4:22am UTC](https://discuss.elastic.co/t/getting-distinct-values/7024 "2012-03-16T04:22:44Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@vijuitech](https://discuss.elastic.co/u/vijuitech)
#### Post date: [March 16, 2012, 4:22am UTC](https://discuss.elastic.co/t/getting-distinct-values/7024/1 "2012-03-16T04:22:44Z")
Hi,
I have a problem on selecting the distinct data based on the name:
The problem is
In a Table which contains the following data:
ID | Name | Age |
| Rank |
| --- |
1 Abc 22 1
2 cbc 20
2
3 Abc 22
1
4 ccc 21
3
5 Abc 22
1
If it is a MySQL Database.. We can select the distinct value by
SELECT \* FROM Table GROUP BY Name;
Is there any alternative in elasticsearch to select the Distinct
Value so we get the output as
ID | Name | Age |
| Rank |
| --- |
1 Abc 22 1
2 cbc 20
2
4 ccc 21
3
Cheers!!
George
---
### Author:  [@vijuitech](https://discuss.elastic.co/u/vijuitech)
#### Post date: [March 16, 2012, 4:29am UTC](https://discuss.elastic.co/t/getting-distinct-values/7024/2 "2012-03-16T04:29:22Z")
Hi,
Please consider this Post
I have a problem on selecting the distinct data based on the name:
The problem is
In a Table which contains the following data:
ID Name Age
* * *
* * *
1 Abc 22
2 cbc 20
3 Abc 22
4 ccc 21
5 Abc 22
If it is a MySQL Database.. We can select the distinct value by
SELECT \* FROM Table GROUP BY Name;
Is there any alternative in elasticsearch to select the Distinct
Value so we get the output as
ID Name Age
* * *
* * *
1 Abc 22
2 cbc 20
4 ccc 21
Cheers!!
George
---
### Author:  [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)
#### Post date: [March 16, 2012, 6:54am UTC](https://discuss.elastic.co/t/getting-distinct-values/7024/3 "2012-03-16T06:54:58Z")
Hi George
> I have a problem on selecting the distinct data based on the name:
> The problem is
>
> In a Table which contains the following data:
>
> ID Name Age
>
> * * *
>
> * * *
>
> 1 Abc 22
> 2 cbc 20
> 3 Abc 22
> 4 ccc 21
> 5 Abc 22
>
> If it is a MySQL Database.. We can select the distinct value by
>
> SELECT \* FROM Table GROUP BY Name;
First, the query that you are using here is a MySQL "feature" (read
"bug"). You are grouping by name, but not telling the database what to
do with the ungrouped columns.
So it should read something like this (depending on what you actually
want):
SELECT Name, min(ID), min(Age) from Table GROUP BY Name.
The Elasticsearch equivalent is called "facets"
> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.
and the particular facet that you should use depends on what other data
you need.
For instance, for just distinct terms, look at the terms facet:
> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.
If you need to group on one field and process another numeric field,
have a look at the terms stats facet:
> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.
etc
clint
> Is there any alternative in elasticsearch to select the Distinct
> Value so we get the output as
>
> ID Name Age
>
> * * *
>
> * * *
>
> 1 Abc 22
>
> 2 cbc 20
>
> 4 ccc 21
>
> Cheers!!
>
> George
---
### Author:  [@vijuitech](https://discuss.elastic.co/u/vijuitech)
#### Post date: [March 16, 2012, 7:48am UTC](https://discuss.elastic.co/t/getting-distinct-values/7024/4 "2012-03-16T07:48:12Z")
Thanks a lot Clinton 🙂
Cheers!!
George
On Mar 16, 11:54 am, Clinton Gormley [cl...@traveljury.com](mailto:cl...@traveljury.com) wrote:
> Hi George
>
> > I have a problem on selecting the distinct data based on the name:
> > The problem is
>
> > In a Table which contains the following data:
>
> > ID Name Age
>
> > * * *
> >
> > * * *
> >
> > 1 Abc 22
> > 2 cbc 20
> > 3 Abc 22
> > 4 ccc 21
> > 5 Abc 22
>
> > If it is a MySQL Database.. We can select the distinct value by
>
> > SELECT \* FROM Table GROUP BY Name;
>
> First, the query that you are using here is a MySQL "feature" (read
> "bug"). You are grouping by name, but not telling the database what to
> do with the ungrouped columns.
>
> So it should read something like this (depending on what you actually
> want):
>
> SELECT Name, min(ID), min(Age) from Table GROUP BY Name.
>
> The Elasticsearch equivalent is called "facets"[Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/)
> and the particular facet that you should use depends on what other data
> you need.
>
> For instance, for just distinct terms, look at the terms facet:[Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-)...
>
> If you need to group on one field and process another numeric field,
> have a look at the terms stats facet:[Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-)...
>
> etc
>
> clint
>
> > Is there any alternative in elasticsearch to select the Distinct
> > Value so we get the output as
>
> > ID Name Age
>
> > * * *
> >
> > * * *
> >
> > 1 Abc 22
>
> > 2 cbc 20
>
> > 4 ccc 21
>
> > Cheers!!
>
> > George
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 3:35am UTC](https://discuss.elastic.co/t/getting-distinct-values/7024/5 "2017-07-06T03:35:58Z")