# InfluxDB query to get distinct values from a table

**URL:** <https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568>\
**Category:** InfluxQL\
**Tags:** influxql\
**Created:** [June 30, 2021, 9:15am UTC](https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568 "2021-06-30T09:15:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sahebdatta](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/sahebdatta/32/6704_2.png) [@sahebdatta](https://community.influxdata.com/u/sahebdatta)\
**Post date:** [June 30, 2021, 9:15am UTC](https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568/1 "2021-06-30T09:15:40Z")

</div>

Hi Guys,

I am working on visualizing InfluxDB data in Grafana. I am having a table that shows the status of the devices connected to my system. The table is updated values every 1 minute (ie my software checks for connection status every minute for all the 500 devices and inserts them in the table those devices that are offline). Now I want to display the result. I display results for the last 1 minute using the query

`SELECT * FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s GROUP BY (*)`

My table looks like this

```auto
Timestamp | Connection | IPLike | EdgeDeviceID | Status
================================================================
12345679 | 192.168.7.14 | 192.168.7. | ED1 | Offline
12345678 | 192.168.14.15 | 192.168.14. | ED2 | Offline
12345667 | 192.168.14.15 | 192.168.14. | ED2 | Offline

```

Sometimes, the query outputs the same device twice.

Now I figured out I can restrict this by using the distinct command.

`SELECT distinct("Connection") FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s`

This solves the repetition problem. But I don’t see the other columns. Can somebody help me with this, please?

Thanks in advance.

Cheers,  
SD

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [July 1, 2021, 12:52am UTC](https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568/2 "2021-07-01T00:52:32Z")

</div>

Hello @sahebdatta,  
Apologies in advanced as my InfluxQL is rusty since I mostly use Flux now,  
but have you tried selecting for the other fields as well?

```auto
SELECT distinct("Connection"), "IPLike", "EdgeDeviceID", "Status" FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s

```

Or whatever your fields are?

---

<div class="post-metadata">

**Author:** ![sahebdatta](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/sahebdatta/32/6704_2.png) [@sahebdatta](https://community.influxdata.com/u/sahebdatta)\
**Post date:** [July 1, 2021, 6:32am UTC](https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568/3 "2021-07-01T06:32:59Z")

</div>

Hi @Anaisdg,

Thanks for the response. I also assumed that this way it should work but unfortunately this returns no results. It shows an error that says

> InfluxDB Error: aggregate function distinct() cannot be combined with other functions or fields

My query looks like this

`SELECT distinct("Connection"), "EdgeDeviceID" FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s`

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [July 1, 2021, 2:51pm UTC](https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568/4 "2021-07-01T14:51:44Z")

</div>

Darn, then I’m not sure how you would do this with InfluxQL in 1.x other than to perform a join with kapacitor. What version of Influx are you using? We could do this with Flux. Otherwise I would suggest making an issue.

---

<div class="post-metadata">

**Author:** ![sahebdatta](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/sahebdatta/32/6704_2.png) [@sahebdatta](https://community.influxdata.com/u/sahebdatta)\
**Post date:** [July 2, 2021, 4:43am UTC](https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568/5 "2021-07-02T04:43:00Z")

</div>

InfluxDB version used: 1.8.x

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [July 2, 2021, 5:50pm UTC](https://community.influxdata.com/t/influxdb-query-to-get-distinct-values-from-a-table/20568/6 "2021-07-02T17:50:16Z")

</div>

If you enable Flux,  
Your query would look something like:

```auto
from(bucket: "my_db")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "my_meas")
  |> filter(fn: (r) => r["_field"] == "Connection" or r["_field"] == "IPLike" or r["_field"] == "EdgeDeviceID" or r["_field"] == "Status" )
  |> schema.fieldsAsCols()
  |> distinct(column: "Connection")

```

And the result will be a table with all of your fields as columns and it will be filtered for the distinct values of the Connection column and the values for the other fields at that those distinct timestamps.

> **[Enable Flux | InfluxDB OSS 1.8 Documentation](https://docs.influxdata.com/influxdb/v1.8/flux/installation/)**
>
> Instructions for enabling Flux in your InfluxDB configuration.