Query to display all distinct rows with all fields

1

Hello everyone,

First of all I’m sorry if this question has been already asked, I couldn’t find any consistent answer. I’m new to influxDB, so I’m probably not doing the right thing, please bear with me. :slight_smile:

Ok, I have a measurement that looks like the one below, and I would like to display only one occurrence based on the “USR” column (FYI, “Date” is set as the only tag)

time                                 USR         ID  State    Date
----                                   ----          --   ------     ----
1543575665033956033 User1     117  1          2018-11-27 17:29:22
1543575672012725616 User1     117  0          2018-11-27 17:29:22
1543575678687792480 User2     114  0          2018-11-27 17:46:04
1543575678687792481 User2     114  0          2018-11-27 17:46:04
1543575681949425390 User3     115  0          2018-11-27 17:46:06
1543575681949425391 User3     115  0          2018-11-27 17:46:08
1543575763613598755 User4     112  0          2018-11-27 17:46:16

I would like to run a query to get and display this result when the field State = 0 :
time USR ID State Date
---- ---- – ------ ----
1543575678687792481 User2 114 0 2018-11-27 17:46:04
1543575681949425391 User3 115 0 2018-11-27 17:46:06
1543575763613598755 User4 112 0 2018-11-27 17:46:16

I’ve tried many thing with distinct(), it works but doesn’t show all the remaining fields, how can I do to get the display I’m looking for ?

Many thanks in advance

2

Hi,
No one can help me with this ? :frowning:

3

Hi can you share this query ?
Best regards

4

hi, sure, here it is, with its result :

> select distinct(USR) from myDB
name: myDB
time distinct
---- --------
0    User1
0    User2
0    User3
0    User4
>

I would like to display all the columns not only USR, but from what I’ve understood with the distinct() function this is a normal behavior, so is there a workaround to display all the columns for the distinct USR values only ?

Thank you for your help.

5

Hi,
I’m still stuck with this… anyone ? :frowning:

6

so none knows the answer to this ?
ok i give up then :frowning:

7

Are you doing this in the CLI?

I’m wondering if the way it displays the results in the CLI is different from the return value from an API call.

8

Hi,
thanks for your reply, in fact I’m doing it through CLI because I need it to be ran in CLI environment.

9

I don’t think you can do what you’re trying to do.

According to the docs you should be able to do something like SELECT DISTINCT(*) to also get other field/tag values, but that has in fact not been supported for a while (see DISTINCT(*) and DISTINCT(*regex*) returning error · Issue #9973 · influxdata/influxdb · GitHub).

10

hi svet,

thank you, now i know that i can’t do it this way.

if somebody knows a workaround to this, i’d be glad to hear from him :slight_smile:

All i want to do is get unique values from a table while displaying all the columns of the table, this must be done from the CLI.

Thanks.