How to get last event for contact

SQL++
1

I am working on a query which will get me a list of all contacts which i can page thru based on what data is past in from query to display in the grid. So far so good. I now have a field in my grid which is called “LastActivity” and that could be either a email, a phone call or other interactions. All of these are stored in individual docs which have a parentId which is equal the contact docId. So how can i get the latest activity based on multiple subqueries and be able to get back the date, the ID and the _type so i can display type and date and use the id to open details if user clicks on this.

2

Please provide sample documents and expected output.

SELECT  c.*, d.*
FROM default AS c
JOIN default AS d  ON KEY d.parentId  FOR c
WHERE c.type  = "contacts" AND d.type =  c.LastActivity;
CREATE INDEX ix1 ON default(parentId);