Can't show distinct value in canvas

[fay]

Hi,
I want to write a sql query to show distinct value in Markdown
I got the value as a table but its duplicate so I want to show the distinct value only!
I try to write
SELECT DISTINCT "Field_name" from "index_name" but I got an error that elastic doesn't support SELECT DISTINCT yet!
also tried
SELECT last_value (DISTINCT "Field_name" ) from "index_name" I got an error ( cannot use distinct on non sql function last_value

any help please ?

[jsanz (Jorge Sanz)]

You need to use GROUP BY to get the distinct values from an index.

For example, having installed the Kibana Flights dataset:

The SQL to get the distinct values of the Carrier field would be like:

SELECT Carrier, count(1) as count
FROM "kibana_sample_data_flights"
GROUP BY Carrier

And then you can use that in the markdown widget with:

# Carriers and counts

| Carrier | Count |
| :--     | --:  |
{{#each rows}}
| {{Carrier}} | {{count}} |
{{/each}}

to get this:

[fay]

Hi jsanz
Thank you for your help
I'm trying what you suggestion and it work
but! I have to order the value by another value field
when I grouped the first field (the field I want it unique ) I can't order by the another field that is not grouped
when I try to group the 2 field and order by wanted field
I got duplicate data again
if you need more explain please tell me

[fay]

Group by query with select case when gives error: Connot use non-grouped column (Canvas elasticsearch sql)

Hello everyone I face a problem with a certain group by elasticsearch sql query when i try to use a field in select case condition but not group by it because it doubles my rows and leaves null values, i get the following error: [image]

my issue looks like this issue