DISTINCT query returns no results, subquery works fine?

1

Hi

I’m using InfluxDB shell version: 1.7.10

And when using this query:

select DISTINCT("INSTANCEID") from (SELECT "INSTANCEID","Users" FROM "instance_metrics" WHERE time > now() - 10m AND "status" =~/4/)

I get no results, but if i run just the subquery i get the results i expect

SELECT "INSTANCEID","Users" FROM "instance_metrics" WHERE time > now() - 10m AND "status" =~/4/


name: instance_metrics
time                INSTANCEID Users
----                ------------------------ -------
1600741611000000000 6857066537460510913      0
1600741611000000000 6888070296062469040      0
1600741611000000000 1263711974140952598      0
1600741611000000000 2593761205830678961      0
1600741611000000000 334541987675708369       0
1600741611000000000 8220649877065111672      0
1600741611000000000 4011148669846043396      0
1600741611000000000 4752283977825580341      0
1600741611000000000 4432841746050330361      0

I would like the INSTANCEID column values to be output as distinct values, i don’t understand why this is not happening.

What is odd, when i remove the status filter it seems to work:

select DISTINCT("INSTANCEID") from (SELECT "INSTANCEID","Users" FROM "instance_metrics" WHERE time > now() - 10m)
name: instance_metrics
time distinct
---- --------
0    1263711974140952598
0    1302080874926140461
0    1626446265035079180
0    1647823715233863344
0    2593761205830678961
0    2599762930576015105
0    334541987675708369

Can anyone help ?

thanks.

2

Some more info might be useful:

  • some sample data (including the status “column”)
  • which tag and fields you have (and the field data type)

Also, what’s “status”? a tag? a field (and of which data type)?
If “Status” is a numeric field you cannot use the regex comparator "status" =~/{exprHere}/ but you have to use the standard "status" = 4

1 Like