title: SQL - where date = max date
body: <p>I've been trying to figure this one out for a while but nothing seems to work for me and maybe a bit of assistance will help me understand it better.  I'm joining multiple tables but only want the max date associated with an ID number.  There could be multiple dates associated with an ID number.  If I take out the MAX(Date) piece, it will run but return multiple date values.  I don't want the MAX(Date) to be in the initial select but only within the WHERE clause.  Here's my query:</p>
  
  <pre><code>SELECT DISTINCT A.ID , C.Date
  FROM IDTable A
  INNER JOIN AccountTable B
     ON B.AccountID = A.AccountID
  FULL OUTER JOIN Updates U
     ON U.ID = A.ID
  FULL OUTER JOIN Update2 C
     ON C.AccountID = B.AccountID
  INNER JOIN UserTable UT
     ON UT.UserMnemonicID = A.AssignedTo
  WHERE A.StatusID NOT IN ('Complete','Open')
     --Need help here to only return MAX date value:
     AND C.Date=(SELECT MAX(C.Date) FROM Update2)
  ORDER BY A.ID
  </code></pre>