Basic Max aggregate


#1

is there another way for getting the json doc associated with a MAX value? This works because I limit it to 1
SELECT *
FROM myBucket AS buck
LET maxClic = (SELECT RAW MAX(clicks) from myBucket)
WHERE buck.clicks <= maxClic
ORDER BY buck.clicks desc
LIMIT 1

But when I say buck.clicks = maxClic, nothing is returned.


#2

maxClic is array due to subquery. You should do maxClic[0]
But you can use following approach for MIN/MAX

SELECT RAW MAX([t.clicks,t])[1] FROM myBucket AS t ;

Explanation given here Select non-group by fields using sql query


#3

Your suggested query works! thank you. But I have two questions. 1. what is [1] for? in your query. 2. maxClic is a LET in my query which will and always will return 1 value - the MAX. Why do you call it an Array? If it is an array, it’ll always be 1 entry and I’d like to use that 1 entry in other where clauses.


#4

got it. Thanks again. But now I’m like why [1] in your query.


#5

Explanation is given above link. Please read here Select non-group by fields using sql query