# Query to display all distinct rows with all fields **URL:** \ **Category:** Systems\ **Tags:** influxdb\ **Created:** [November 30, 2018, 12:35pm UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689 "2018-11-30T12:35:32Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Eagle](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@Eagle](https://community.influxdata.com/u/Eagle)\ **Post date:** [November 30, 2018, 12:35pm UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/1 "2018-11-30T12:35:32Z")
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. 🙂 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 ---
**Author:** ![Eagle](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@Eagle](https://community.influxdata.com/u/Eagle)\ **Post date:** [December 4, 2018, 3:54pm UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/2 "2018-12-04T15:54:33Z")
Hi, No one can help me with this ? ☹ ---
**Author:** ![MarcV](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/marcv/32/7676_2.png) [@MarcV](https://community.influxdata.com/u/MarcV)\ **Post date:** [December 5, 2018, 7:40am UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/3 "2018-12-05T07:40:28Z")
> [@Eagle](#): > > it works but doesn’t show all the remaining fields Hi can you share this query ? Best regards ---
**Author:** ![Eagle](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@Eagle](https://community.influxdata.com/u/Eagle)\ **Post date:** [December 5, 2018, 8:32am UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/4 "2018-12-05T08:32:09Z")
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. ---
**Author:** ![Eagle](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@Eagle](https://community.influxdata.com/u/Eagle)\ **Post date:** [December 7, 2018, 9:47am UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/5 "2018-12-07T09:47:03Z")
Hi, I’m still stuck with this… anyone ? ☹ ---
**Author:** ![Eagle](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@Eagle](https://community.influxdata.com/u/Eagle)\ **Post date:** [December 19, 2018, 6:25pm UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/6 "2018-12-19T18:25:15Z")
so none knows the answer to this ? ok i give up then ☹ ---
**Author:** ![katy](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/katy/32/855_2.png) [@katy](https://community.influxdata.com/u/katy)\ **Post date:** [December 19, 2018, 6:57pm UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/7 "2018-12-19T18:57:31Z")
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. ---
**Author:** ![Eagle](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@Eagle](https://community.influxdata.com/u/Eagle)\ **Post date:** [December 21, 2018, 9:54am UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/8 "2018-12-21T09:54:08Z")
Hi, thanks for your reply, in fact I’m doing it through CLI because I need it to be ran in CLI environment. ---
**Author:** ![svet](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/svet/32/7092_2.png) [@svet](https://community.influxdata.com/u/svet)\ **Post date:** [December 22, 2018, 5:04pm UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/9 "2018-12-22T17:04:28Z")
I don’t think you can do what you’re trying to do. According to [the docs](https://docs.influxdata.com/influxdb/v1.7/query_language/functions/#distinct) 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](https://github.com/influxdata/influxdb/issues/9973)). ---
**Author:** ![Eagle](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@Eagle](https://community.influxdata.com/u/Eagle)\ **Post date:** [December 22, 2018, 6:05pm UTC](https://community.influxdata.com/t/query-to-display-all-distinct-rows-with-all-fields/7689/10 "2018-12-22T18:05:27Z")
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 🙂 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.