How to return the most recent record when using the 'min' function and multiple records exist with the same min value

Grafana Dashboards
1

Hi Team, hope someone can help me with this one.
I’m not an sql wiz, so bear with me…

I am using Grafana v 9.47 on a Raspberry pi

I am using a STAT visualisation and I’d like to retrieve the most recent ‘min’ value record when there are multiple records with the same min value. in the example below, I’m looking for the min battery voltage over the last 7 days. If the min voltage was 13.2 v and the battery reading was 13.2v both 2 days ago and 6 days ago. The record to be retrieved is the record from 6 days ago. I would like the most recent record (2 days ago) retrieved

My query looks like this but it returns the oldest record to match the min value

SELECT min(“value”) FROM “electrical.batteries.0.voltage” WHERE time >= now() - 7d and time <= now()

How to I retrieve the most recent record? In my case above, this would be the record from 2 days ago

2

Thus piece is a bit confusing . So you want 6 days ago or 2 days ago?

What datanase type is it?

3

sorry for the delay.
It’s an influxDB database and I would like to retrieve the minimum value within a certain time period (for example last 6 days). When there are 2 or more records with the same min value, I would like the most recent record. Currently, I get the oldest record.

Hope that helps to explain

4

Easy to do if you are willing to migrate to Flux.

1 Like
5

I had not considered moving to another DB - was hoping to stick with influxDB.

6

You are currently using InfluxQL. Which version of InfluxDB are you using? It may support Flux.

More here: How to Build Grafana Dashboards with InfluxDB, Flux, and InfluxQL | InfluxData

7

pi@raspberrypi:~ $ influx
Connected to http://localhost:8086 version 1.8.10
InfluxDB shell version: 1.8.10

8

Acc. to the tutorial that I sent, it should support Flux if you enable it.

1 Like
9

Thanks Grant, let me try that and get back to you