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

I'm not an Influx expert, but my guess is that the second query you are trying is not supported. — Tim Biegeleisen, Aug 29 '16 at 23:28
@TimBiegeleisen Both are working on String Column. Wondering why first is working on value column. — Ammad, Aug 29 '16 at 23:50
What @MichaelDesa said. What is the output from "SHOW FIELD KEYS FROM interface" and "SHOW TAG KEYS FROM interface"? — Jason, Sep 09 '16 at 05:49

score 27 · Answer 1 · answered May 29 '18 at 10:16

27

There is a way of solving this by using double SELECT statement:

> select distinct("tag1") from (select "field1", "tag1" from foo)

The inner query returns field1 and tag1 which can be queried outside like normal fields for which you can apply distinct().

Hope it helps. Cosmo.

answered May 29 '18 at 10:16

Cosmo

271
3
3

2

this returns zero rows for me on influxDB v1.6 – Alexander Sep 21 '18 at 07:22
Can you try the inner query alone first? Does it return any dat? If yes check the name of columns returned and make sure you have the same name for eg “tag1” in the outer query as in the output of your inner query. Hope it helps. – Cosmo Sep 22 '18 at 08:13
yep, I'm on 1.6.1 and unfortunately can confirm that this does not work. inner query returns expected results (as does selecting the field in the outer select), but not once you add distinct – Christopher Kuttruff Dec 14 '18 at 23:47
2

This works for me, but if I have a gazillion rows in a table but only 10 distinct tags, then it's going to sort a gazillion rows to run the distinct function. How about limiting the data retrieved by the "inner" query like this: `select distinct("tag1") from (select "field1", "tag1" from foo where time>now()-10m)` Note that "inner" is maybe a bad term to use because "inner" queries typically refer to relational table join types in other domains – Ben Slade Jul 09 '19 at 17:09

Jason · Accepted Answer · 2016-09-13T04:48:39.897

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.

score 24 · Answer 3 · answered Sep 24 '18 at 15:12

24

There is SHOW TAG VALUES WITH key = path that one can use to get unique Tag values

answered Sep 24 '18 at 15:12

Alexander

883
7
17

1

Nice! Any idea how to limit this to a measurement? – Dan Dascalescu Dec 29 '18 at 02:28
6

To limit to a measurement, one can do SHOW TAG VALUES FROM "measurement name" with key = path – Ramesh N Apr 24 '19 at 19:13
to select distinct tags based on a condition on a differet tag you can do : `SHOW TAG VALUES WITH key = someKey where someOtherKey = 'someValue'` – mouserat Jun 03 '20 at 08:15

score 7 · Answer 4 · edited Aug 15 '20 at 09:51

7

SHOW TAG VALUES from "measurements" WITH key = path

for example:

SHOW TAG VALUES from redis with key="server"

edited Aug 15 '20 at 09:51

Balázs Kovacsics

693
5
17

answered Aug 14 '20 at 07:39

G.S

81
1
1

Why the downvote on this answer ? Works great for me and it's exactly what I was looking for. – Mathieu Rollet Nov 17 '20 at 13:44

How to use Distinct function in influxDB

4 Answers4

Short Answer

Long Answer