# Can't show distinct value in canvas
**URL:** https://discuss.elastic.co/t/cant-show-distinct-value-in-canvas/345552
**Category:** Kibana
**Tags:** canvas
**Created:** [October 23, 2023, 9:21am UTC](https://discuss.elastic.co/t/cant-show-distinct-value-in-canvas/345552 "2023-10-23T09:21:52Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@fay](https://discuss.elastic.co/u/fay)
#### Post date: [October 23, 2023, 9:21am UTC](https://discuss.elastic.co/t/cant-show-distinct-value-in-canvas/345552/1 "2023-10-23T09:21:52Z")
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 ?
---
### Author:  [@jsanz](https://discuss.elastic.co/u/jsanz)
#### Post date: [October 25, 2023, 9:04am UTC](https://discuss.elastic.co/t/cant-show-distinct-value-in-canvas/345552/2 "2023-10-25T09:04:39Z")
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:
```sql
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:
```markdown
# Carriers and counts
| Carrier | Count |
| :-- | --: |
{{#each rows}}
| {{Carrier}} | {{count}} |
{{/each}}
```
to get this:
---
### Author:  [@fay](https://discuss.elastic.co/u/fay)
#### Post date: [October 25, 2023, 11:21am UTC](https://discuss.elastic.co/t/cant-show-distinct-value-in-canvas/345552/3 "2023-10-25T11:21:55Z")
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
---
### Author:  [@fay](https://discuss.elastic.co/u/fay)
#### Post date: [October 25, 2023, 11:42am UTC](https://discuss.elastic.co/t/cant-show-distinct-value-in-canvas/345552/4 "2023-10-25T11:42:26Z")
> [@Group by query with select case when gives error: Connot use non-grouped column (Canvas elasticsearch sql)](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880):
>
> 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
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 22, 2023, 11:43am UTC](https://discuss.elastic.co/t/cant-show-distinct-value-in-canvas/345552/5 "2023-11-22T11:43:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.