Query to display all distinct rows with all fields

influxdb

#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: