I am attempting to select one record (the Max(StartDate)) for each ExpertID using the query below. I have manged to do this, but in the event that the only record for an ExpertID has no StartDate, i would like to return it as well. This i cannot seem to figure out.
SELECT ProjectExperts.ProjectExpertID, ProjectExperts.ProjectID, ProjectExperts.ExpertID, ProjectExperts.StartDate, ProjectExperts.EndDate
WHERE (((ProjectExperts.StartDate)=(SELECT Max(ProjectExperts_2.StartDate) FROM ProjectExperts AS ProjectExperts_2 WHERE ProjectExperts_2.ExpertID = ProjectExperts.ExpertID)));
I haven't gone into the syntax but I think what may work is the following:
WHERE ([DATE] = SELECT MAX()...................)
OR (ISNULL([DATE]) AND COUNT([ID]) = 1 WHERE ...)
There are two components to the outer where clause. The first is the same as you have already. The second specifies only one occurrence for this expert and the start date is missing. Should work because the two conditions are mutually exclusive.
Got a feeling it might be quite slow too if you have a big table.
from ProjectExperts as X
where StartDate =
( select max(StartDate)
where ExpertID = X.ExpertID )
or not exists
( select StartDate
where ExpertID = X.ExpertID
and StartDate is not null )