How to use Distinct function in influxDB

Question

I am using influx DB and issuing command,

SELECT * FROM interface

Below is the out put-

interface 
time                              element                path                                       value
2016-08-24T21:22:16.7080877Z    "link-layer-address0"   "key:/arp-information/link-layer-address0"  "3c:61:04:48:df:91"
2016-08-24T21:22:17.9090527Z    "link-layer-address0"   "key:/arp-information/link-layer-address0"  "3c:61:04:48:df:92"
2016-08-24T21:22:19.8584133Z    "link-layer-address1"   "key:/arp-information/link-layer-address1"  "3c:61:04:48:df:97"
2016-08-24T21:22:20.3377847Z    "link-layer-address2"   "key:/arp-information/link-layer-address2"  "3c:61:04:48:df:90"

When issue command it works fine.

SELECT distinct(value) FROM interface 

But When issue command for path column there is no out put. Wondering what i am missing?

SELECT distinct(path) FROM interface 

Answer 1

Thanks for the extra info @Ammad.

Short Answer

Try GROUP BY with tags. DISTINCT() only works with fields.

Long Answer

distinct() works on fields, not tags. See here:

https://docs.influxdata.com/influxdb/v1.0/query_language/functions/#distinct

DISTINCT() returns the unique values of a single field.

The field values are meant to be the actual data you're interested in. Tag values are metadata: data about the data. Most functions in database systems operate on the data or the metadata, but rarely on both.

Here's a toy example on v0.13, showing that distinct() really doesn't work with tags:

insert foo,tag1=asdf field1="some text"
insert foo,tag1=asdf field1="some text"
insert foo,tag1=asdfg field1="some text"
insert foo,tag1=asdfg field1="some text"
insert foo,tag1=asdfg field1="some more text"
insert foo,tag1=asdfg field1="some more text"

Now some queries:

select * from foo

name: foo
time                            field1          tag1
2016-09-12T05:19:53.563221799Z  some text       asdf
2016-09-12T05:20:03.027652248Z  some text       asdf
2016-09-12T05:20:10.04939971Z   some text       asdfg
2016-09-12T05:20:11.235525548Z  some text       asdfg
2016-09-12T05:20:17.418920163Z  some more text  asdfg
2016-09-12T05:20:19.354742922Z  some more text  asdfg

Now let's try distinct()

select distinct(tag1) from foo

Results in no output at all.

select distinct(field1) from foo

name: foo
time                    distinct
1970-01-01T00:00:00Z    some text
1970-01-01T00:00:00Z    some more text

You might be able to get what you want by using GROUP BY. Like this:

select distinct(field1) from foo group by tag1

Which gives:

name: foo
tags: tag1=asdf
time                    distinct
1970-01-01T00:00:00Z    some text

name: foo
tags: tag1=asdfg
time                    distinct
1970-01-01T00:00:00Z    some text
1970-01-01T00:00:00Z    some more text

This shows each value of tag1, and the values of field1 associated with that tag1 value.

Hope that helps.