title: MS SQL select multiple columns based on the MAX(ID) with DISTINCT
body: <p>I'm trying to merge two queries. The aim is to get a recordset that has unique customer emails along with their name and the title for their latest listing based on MAX(ID).</p>
  
  <p>Using DISTINCT I can get the email and ID but obviously adding the title breaks this.</p>
  
  <p>This is what I have so far:</p>
  
  <pre><code>SELECT DISTINCT MAX(EV_ID) As EV_ID, EV_ContactEmail, EV_CusName
  FROM tblEvents ev
  INNER JOIN tblCustomers cus ON cus.CUS_ID = ev.EV_CustomerID
  WHERE (
      CUS_IsAdmin = 'y'
      AND CUS_Live = 'y' 
      AND EV_Live = 'y' 
      AND EV_EndDate &gt;= '2012/7/5 12:00:00 AM'
      AND EV_ContactEmail &lt;&gt; ''
      )
  GROUP BY EV_ContactEmail
  </code></pre>
  
  <p>I've found some posts on here that say indicate I need to do a self join but I cannot get it to return the same amount of records but with the title, it returns many more.</p>
  
  <pre><code>SELECT DISTINCT MAX(EV_ID) As EV_ID, ev.EV_Title, EV_ContactEmail, EV_CusName
  FROM tblEvents ev
  INNER JOIN tblCustomers cus ON cus.CUS_ID = ev.EV_CustomerID
  INNER JOIN (
      SELECT EV_Title, MAX(EV_ID) AS MaxID
      FROM tblEvents
      GROUP BY EV_Title
  ) groupedev ON ev.EV_Title = groupedev.EV_Title AND ev.EV_ID = groupedev.MaxID
  WHERE (
      CUS_IsAdmin = 'y' 
      AND CUS_Live = 'y' 
      AND EV_Live = 'y' 
      AND EV_EndDate &gt;= '2012/7/5 12:00:00 AM'
      AND EV_ContactEmail &lt;&gt; ''
  )
  GROUP BY EV_ContactEmail, ev.EV_Title
  </code></pre>
  
  <p>Can anyone advise what is wrong with it?</p>