Help with correlated subquery please

SQL++
1

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)[0]
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.

2

N1QL query not right due to USE KEYS t1,t2 always same it gives same row as t1.
As you want each BirdId you want the record that has latest dateTime

SELECT  doc.dateTime, doc.birdId, doc.island
FROM `bird` AS t1
WHERE t1.docType = 'Record' AND t1.birdId IS NOT NULL
GROUP BY t1.birdId
LETTING doc = MAX([t1.dateTime,{t1.dateTime, t1.birdId, t1.island}])[1]
ORDER BY birdId;

OR

SELECT  t1.birdId,  doc[0] AS dateTime, doc[1] AS island
FROM `bird` AS t1
WHERE t1.docType = 'Record' AND t1.birdId IS NOT NULL
GROUP BY t1.birdId
LETTING doc = MAX([t1.dateTime, t1.island])
ORDER BY birdId;

CREATE INDEX ix1 ON `bird`(birdId, dateTime,island) WHERE docType = "Record";

Read Select non-group by fields using sql query for understanding the technique.

3

Excellent! Both of those are working. The second one is even slightly faster than the first. Thanks for that :slight_smile: