SQL CE Filter by Effective Date using SubSelect

I am trying to filter by effective date in my SQLce (Versio开发者_如何学Gon 3.5) database in C#. Here is my Query:

SELECT        FirstName, LastName, HomeID, ConditionID, ADate, OwnerID
FROM            Sys_HomeOwner
WHERE        (ADate =
                             (SELECT        MAX(ADate) AS Expr1
                               FROM            Sys_HomeOwner AS Sys_HomeOwner_1))

Error returned is:

There was an error parsing the query. (token line number=1, token line offset=118, token in error=SELECT]

I have been doing some querying and it seems it is possible to do subselects in SQLCE. If i pull out the subselect it runs OK for the table as a whole anyway pulling the max date.

What am i doing wrong?

thanks for the help.

You mention getting each rows maximum, I think you need to use a GROUP BY for that.

How about:

SELECT FirstName, LastName, NameID, ConditionID, ADate, OwnerID
FROM Sys_HomeOwner h
LEFT OUTER JOIN (SELECT MAX(ADate) AS maxdate, HomeID
                 FROM Sys_HomeOwner
                 GROUP BY HomeID) AS effectiveDates
     ON h.HomeID = effectiveDates.HomeID
WHERE h.ADate = effectiveDates.maxdate

Assuming you want to get the latest dates for each HomeID

SELECT TOP 1 FirstName, LastName, HomeID, ConditionID, ADate, OwnerID 
FROM Sys_HomeOwner 
ORDER BY ADate DESC