title: Selecting the top matching record of a one to many relationship on a SQL join statement
body: <p>I have a question that will probably something simple but I am not a SQL expert by any stretch.
  I have this query that has 3 join, the 3rd one is a one to many relationship and I am trying to get the top 1. Which in this case would be the last edit on a support call.</p>
  
  <p>The info comes for 4 tables, CASE, for the basic case details, CONTACT1 and CONTACT2 for the contacts information and NOTES for the notes of said case, This NOTES table creates 1 entry for each notes entered in that case. I want to create a dashboard that will include that last entry time stamp.</p>
  
  <p>I tried to add a Select top (1) within the join, tried all possible options for the join and use Max (C3.MODIFIEDDATE) with Group By but I always end up with all the records in NOTES for each ticket.</p>
  
  <p>Here is my Query:</p>
  
  <pre><code>SELECT c.NUMBER as [Case Number], c.OWNER as [Assigned To], case c.status when 0 then '&lt;unknown&gt;' when 1 then 'Assigned' when 2 then 'Reassigned' when 3 then 'Escalated' when 4 then 'Resolved' 
  else cast(c.status as varchar(50)) end as [Case Status], c.SUBJECT as [Subject], c.OFFERING as [Next Step], DATEADD(day,DATEDIFF(day,0,c.CREATED_DATE),0) as [Created on], c1.CONTACT 
  as [Primary Contact], c1.COMPANY as [Company], c3.MODIFIEDDATE as [Last Edit], c3.MODIFIEDBY as [Last Agent]
  
  FROM CASES c left join(CONTACT1 c1 inner join CONTACT2 c2 on c1.ACCOUNTNO = c2.ACCOUNTNO ) on c1.ACCOUNTNO = c.ACCOUNTNO 
     left join NOTES as c3 on c3.LOPRECID =c.recid -- That's the one that has a 1 to many resolve
  
  
  WHERE (  c.IS_TEMPLATE = 0  AND   c.STATUS IN ( 1,2,3 ) and c3.rectype='CS' )
  ORDER BY [Company] asc, c3.modifieddate desc
  </code></pre>
  
  <p>Any suggestion would be apreciated</p>