Can't show distinct value in canvas

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 ?

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

to get this:


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

my issue looks like this issue

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.