Best performance Sub Queries or Joint

SQL++
1

I have 2 Buckets , on holds a a group of docs lets call it farm which the user uses to track his marketing activities in the real-estate market. Each property is a document and holds a value of APN which is a Parcel number. On the average users have between 1000 and 10000 of these docs. Then i have a bucket dedicated to a live feed of MLS data which updates all transactions every 5 minutes. Any APN can have none or 1 or more records in that feed, as each record refers to a listing so if a house was sold twice over last 2 years it has 2 records.
So what i want is to pull and join based on first list of docs the latest (newest) doc from my feed including the Status, and Listing ID. So what would the best approach be if i have to query from different buckets and want more then one field from second table. Normally subquery does a single field but not sure if a joint can do based on a max value of a date to get the newest. In subquery i could do where status <> ‘closed’ Order by listingdate DESC Limit 1 which would get me only the newest record if it is not closed.

2

Hi @makeawish,

In addition to N1QL solutions (sorry not my specialty), You might also consider Eventing to update this information on mutations in real-time. There is an example you might want to look at Keep the Last N User Items it has two variants

You can also look up other docs by KEY (or even N1QL from the Eventing function itself) and update your Status and Listing ID.

Jon Strabala