Query second (n-th) last value?

1

Is it possible to query only second (n-th) last value from a measurement?

2

A bit ugly, but should work:

Select value from (select value from table where whatever order by time desc
limit 2) order by time limit 1;

Antony.

3

If I am running this query then I am getting an error that

subqueries must be ordered in the same direction as the query itself

4

Meh. I don’t think I have an alternative idea, then. Hopefully someone else
has.

Antony.

5

After much hit and trial, I found a way
To get 2nd last value:
SELECT st FROM (SELECT value as st FROM Value WHERE whatever ORDER BY time DESC LIMIT 2 OFFSET 1) ORDER BY time DESC LIMIT 1

To get 3rd last value:
SELECT st FROM (SELECT value as st FROM Value WHERE whatever ORDER BY time DESC LIMIT 3 OFFSET 2) ORDER BY time DESC LIMIT 1

6

any idea how to perform this with flux?

7

@phil333

The only way that I can guess this might be possible in Flux is if you knew how many records (rows) were being returned, and you used the limit() function. For example, if the table had 15 rows and you wanted the 13th row, you would use

|> limit(n: 13)

followed by the last() function.

8

Awesome @grant1 that works perffectly. These are the flux lines I used for the 4th last element

|> sort(columns: [“_time”],desc:true)
|> limit(n: 4)
|> last()