how to find max and min values randomly

[SRI8273]

load * Inline [
ID,Date,Customer,SalesA
1,1@1@2022,sri1,100
2,2@1-2022,sri2,200
3,3@1@2022,sri3,600
4,4@1@2022,sri4,1800
5,5@1@2022,sri5,10000
6,6@1@2022,sri6,25000
7,7@1@2022,sri7,1500
8,8@1@2022,sri8,2000
9,9@1@2022,sri9,4500
10,10@1@2022,sri10,8000
]
;

i want to fetch random max values like 2nd max value from the data

and 5th min value from the data

thanks in advance.

[justISO]

Hi, not so elegant solution, but you could try something like this:

sample:
load * Inline [
ID,Date,Customer,SalesA
1,2022-01-01,sri1,100
2,2022-01-02,sri2,200
3,2022-01-03,sri3,600
4,2022-01-04,sri4,1800
5,2022-01-05,sri5,10000
6,2022-01-06,sri6,25000
7,2022-01-07,sri7,1500
8,2022-01-08,sri8,2000
9,2022-01-09,sri9,4500
10,2022-01-10,sri10,8000];

JOIN LOAD //ranking sales from lowest to highest
*,
RowNo() as rowMIN
RESIDENT sample
ORDER by SalesA asc;

JOIN LOAD //ranking sales from highest to lowest
*,
RowNo() as rowMAX
RESIDENT sample
ORDER by SalesA desc;

LET vNoR = NoOfRows('sample'); //nbr of rows
LET vRand1 = ROUND(rand()*vNoR,1); //random number 1
LET vRand2 = ROUND(rand()*vNoR,1); //random number 2

NoConcatenate
min_max:
LOAD
ID, Date, Customer, SalesA, 
if(rowMAX = $(vRand1), $(vRand1) & ' max', if(rowMIN = $(vRand2), $(vRand2) & ' min')) as ind
RESIDENT sample
WHERE rowMAX = $(vRand1) or rowMIN = $(vRand2);

DROP TABLE sample;