Help picking one document each in List of objects

SQL++
1

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.

2 
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
3

Hi @vsr1 ,
Thanks for the reply, There is a small change in requirement, where we can have documents with null Expirytime, so in that scenario this query is not picking any such documents.

Can you tell how we can still sort this documents by expiry time, and if expiry time is null we have to treat it as infinite value . Effective time will always be present.

Thanks,
Ashish