1
    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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GorillaApe
  • 3,611
  • 10
  • 63
  • 106

2 Answers2

3

Here is one way to do it, there are other ways which can be faster, but I believe this one to be the clearest for a beginner.

 SELECT BabyInformation.*, BabyData.*
    FROM   BabyInformation 
    JOIN
      (SELECT BabyID, Max(Date) as maxDate  FROM  BabyData 
       GROUP BY BabyID 
      ) AS t1 
 ON BabyInformation.BabyID=t1.BabyID 
    Join BabyData ON BabyData.BabyID = t1.BabyID and BabyData.Date = t1.maxDate
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • +1 for clarity! This would work, though I think joining on a date like this could produce unpredictable results if two or more rows were inserted in the BabyData table for the same Baby simultaneously. Probably not an issue in this case (it's what the question asked for after all) - but worth mentioning. – Will Oct 14 '10 at 10:34
  • SELECT BabyID, Max(Date) as maxDate,Length,Weight wont work.. also babyinformation doesnt contain date – GorillaApe Oct 14 '10 at 10:47
  • @Parhs, Can't help you if you don't tell me what your tables contain. What do you tables contain and what information do you want. – Hogan Oct 14 '10 at 10:55
  • @Parhs, fixed it. But I did not test -- might have typos. – Hogan Oct 14 '10 at 10:58
  • That worked! Thank you.. I just put LEFT join because i want to display all rows from BabyInformation... I hope that this is correct – GorillaApe Oct 14 '10 at 11:02
  • 1
    A small problem only.If date is same it dublicates – GorillaApe Oct 14 '10 at 11:10
  • "If date is same it dublicates" - this was the behaviour I mentioned in my first comment, it's caused by joining on a column that can have duplicates. – Will Oct 14 '10 at 12:02
  • @Parhs : LEFT join is correct if you want to see items that don't have records in the BabyData table. – Hogan Oct 15 '10 at 03:02
  • For all who comment about dups -- this should be a non-issue in most cases since date includes millsecs -- you can add a constraint on the table to not allow duplicates of id+date if the system could possibly store such data. – Hogan Oct 15 '10 at 03:05
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.

Will
  • 346
  • 1
  • 4
  • There was an error parsing the query.Token in error SELECT (the last) – GorillaApe Oct 14 '10 at 10:39
  • 1) Are you executing this in SQL Management Studio, or from your code? 2) Is there a field called BabyDataId in the table BabyData? (I assumed that there was - but you didn't mention it..) – Will Oct 14 '10 at 10:45
  • Apologies - there was an error, which I've updated. Should work now. – Will Oct 14 '10 at 11:59
  • thanks:) But i set date as primary key and no problem... I dont need more than 1 per day.What was the error exaclty? – GorillaApe Oct 14 '10 at 12:19
  • I used the incorrect table alias in the subquery... it said bd.BabyId instead of bi.BabyId - that'll teach me not to proofread :) – Will Oct 14 '10 at 12:53
  • This will be much slower than my query since it does a select for each row in the babyinformation table – Hogan Oct 15 '10 at 03:07
  • @Hogan it will be slower...though your query requires that an extra constraint be added to the table to guarantee uniqueness of the Date. I agree that your solution is faster (that's why I gave you an upvote), though clearly each solution has it's downside. – Will Oct 15 '10 at 08:55