How do i write this n1ql

SQL++
, ,
1

Hi Team ,

I have a database where i have the index on the type of document.
I have a requirement like this

Select data.prodId , data.compId , vwTm ,a.*
from api_external a
where type = "prodVw" 
group by data.prodId , data.compId 
having vwTm = ( select min(vwTm) from api_external b  where b.data.prodId = a.data.prodId  and b.data.compId = a.data.prodId )

Means want to select all document attributes where viewtime is minimum for a prodID and compId.

The thing is we need the rest of doument attributes as well . How do we write this query ?

Thanks a lot for your time

Thanks

2 
CREATE INDEX ix1 ON api_external ( data.compId, data.prodId, vmTm ) WHERE type = "prodVw" ;
WITH docs AS (SELECT RAW MIN([a.vwTm, MTEA(a).id])[1]
                         FROM api_external AS a
                         WHERE a.type = "prodVw" AND a.data.compId IS NOT NULL
                         GROUP BY a.data.prodId , a.data.compId )
SELECT a.* FROM api_external AS a USE KEYS docs;
3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.