Help picking one document each in List of objects

Hi @vsr1,

I have one query can you help on this.
Below is my query

SELECT FROM publish
WHERE documentId IN [“AAA”,BBB",“CCC”]
AND type=“page”
AND ( (effectiveTime <= 1606133812855 AND 1606133812855 <= expiryTime)
OR (effectiveTime > 1606133812855) )
ORDER BY expiryTime ASC

I have bucket publish with hundreds of document, each document has a documentId , I want to pick at least one document each having id as AAA or BBB or CCC. and further have some time-based logic to filter the document as each id will have multiple documents separated by their running time. this query is working fine but it returns multiple documents for each id as effectiveTime > 1606133812855 will pick all future documents, but what I want is after the order by expirytime, the first document from each should be picked. Can you help me in achieving this, as I don’t want to sort this in my code after getting the results?

Thank you.

SELECT MIN([ expiryTime, p])[1].*
FROM  `publish` AS p
WHERE documentId IN [“AAA”,BBB",“CCC”]
AND type=“page”
AND ( (effectiveTime <= 1606133812855 AND 1606133812855 <= expiryTime)
OR (effectiveTime > 1606133812855) )
GROUP BY documentId;
1 Like