I am wondering if the following query can be done entirely in code (perhaps using a TypedList), or if I need to resort to a TypedView.
In a nutshell, my DB contains documents, each which contains multiple sections, and each section contains multiple events. I want to return the latest event of a certain type among all of the sections of a particular document.
SELECT MAX(EventTime) AS LatestEventTime,LatestEditSect.DocumentID FROM
(SELECT MAX(EventTime) AS SectEventTime,DocSectionID
FROM DocSectionEvent
WHERE DocSectionEventTypeID IN (8,12)
GROUP BY DocSectionID) LatestSectEditEvent
INNER JOIN DocSection LatestEditSect ON LatestSectEditEvent.DocSectionID=LatestEditSect.DocSectionID
GROUP BY LatestEditSect.DocumentID