Hi I’m trying to execute a correlated sub query in N1QL but I can’t get it to work. This is the query that works fine in SQL against the old system’s relational database.
Select distinct t1.birdid, t1.Island, t1.DateTime from Recovery t1 where t1.DateTime = (Select Max(t2.DateTime) from Recovery t2 where t1.BirdID = t2.BirdID) Order by BirdID
This is my best (!??) attempt at translating this into N1QL.
Select t1.birdID, t1.island, t1.dateTime from `bird` as t1 where t1.docType = 'Record' and t1.dateTime = (Select raw max(t2.dateTime) from `bird` as t2 USE KEYS meta(t1).id where t1.birdID = t2.birdID) order by birdID limit 30
In the new system Recovery has been renamed to Record.
The relational query gives me one row per Bird with the latest Island the bird has been recorded at.
But the N1QL version returns every Record document of every Bird.
Not sure what I’m missing here, and I’ve tried a bunch of other things. Any help would be much appreciated.