Getting Distinct Values

Elastic Stack Elasticsearch
1

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

2

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

3

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"

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:

If you need to group on one field and process another numeric field,
have a look at the terms stats facet:

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

4

Thanks a lot Clinton :slight_smile:

Cheers!!

George

On Mar 16, 11:54 am, Clinton Gormley 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
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...

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...

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