SQL query for SQL Server Compact Edition 3.5 - GROUP BY issue

Question

    SELECT BabyInformation.* , t1.* 
    FROM   BabyInformation 
    LEFT  JOIN
      (SELECT *  FROM  BabyData 
       GROUP BY BabyID 
       ORDER By Date DESC  ) AS t1 ON BabyInformation.BabyID=t1.BabyID

This is my query. I want to get the one most recent BabyData tuple based on date. The BabyInformation should left join with babyData but one row per baby...

I tried TOP(1) but this worked only for the first baby

Answer 1

This should do it:

SELECT bi.* , bd.* 
    FROM BabyInformation [bi]
    LEFT JOIN BabyData [bd]
        on bd.BabyDataId = (select top 1 sub.BabyDataId from BabyData [sub] where sub.BabyId = bi.BabyId order by sub.Date desc)

I've assumed that there is a column called 'BabyDataId' in the BabyData table.