I'm trying to return a field from the most recent record in a joined (related) table. I might have done this with a nested select statement but that is not supported in BigQuery.

```
Table 1: tblIncidents
- incidentId
- incidentName
- clientName
- incidentDescription
- incidentDate

```

```
Table 2: tblActivity
- activityId
- incidentId
- activityDate
- activityStatus

```

There could be between 0 and n records in tblActivity for a given incident.

I'll be displaying several incidents based on the `incidentDate`.

Also, if there are no activity records, the field can be null or perhaps I could set a string value of "no activity".

I know this doesn't work but something like this would in MS SQL Server. I'm unsure of the best way to do this in BigQuery.

```
Select I.incidentId, I.incidentDate, I.clientName, I.incidentName, 
(Select A.activityStatus FROM tblActivity A 
Where A.incidentId = I.incidentId as activityStatus ORDER BY A.activityDate DESC limit 1) as activityStatus,
incidentDescription 
FROM tblIncidents I WHERE clientName = 'a given client name'

```

Thank you in advance for any insight into which direction to go with this.