title: sql eliminate dupliates with Max Date
body: <p>I have the following query:</p>
  
  <pre><code>(
   Select Event.EventID, Event_Reps.LastName , Event_reps.FirstName, Cost.EventDate
   From Cost WITH(NOLOCK) 
   inner join Event WITH(NOLOCK) on Cost.EventID = Event.EventID 
   inner join Event_Reps WITH(NOLOCK) on Cost.EventRep = Event_Reps.EventRep 
   where Event.EventID = 66
   and Event_Reps.Division = 'Houston' and Cost.CostAmt = 0 
   ) as table1
  </code></pre>
  
  <p>As there are duplicate records in the Cost table for a given EventID,  I need to find the one that has the greatest EventDate.
  I am not sure how to change the above query such that it does what it does but eliminates the duplicates and give me the Cost record that has the max EventDate. </p>