title: How to display distinct values based on MAX date in report builder?
body: <p>I'm quite new to SQL and I hope you can help me.
  I'm trying to retrieve unique values from my table based on the latest date where specific users are selected.</p>
  
  <p>This is the data:</p>
  
  <p><a href="https://i.stack.imgur.com/oJAf6.png" rel="nofollow noreferrer">Raw Data</a></p>
  
  <p>And this is what I'm looking to achieve:</p>
  
  <p><a href="https://i.stack.imgur.com/NWgHb.png" rel="nofollow noreferrer">Desired Data</a></p>
  
  <p>I tried to write 2 queries but unfortunately:</p>
  
  <p>My 1st query would display duplicated rows for each company:</p>
  
  <pre><code>SELECT DISTINCT FilteredAppointment.regardingobjectidname ,FilteredAppointment.owneridname ,FilteredAppointment.subject ,MAX(FilteredAppointment.scheduledstart) as Date ,FilteredAppointment.location ,FilteredCcx_member.ccx_mnemonic FROM FilteredAppointment INNER JOIN FilteredAccount ON FilteredAppointment.regardingobjectid = FilteredAccount.accountid INNER JOIN FilteredCcx_member ON FilteredAccount.accountid = FilteredCcx_member.ccx_accountid WHERE FilteredAppointment.statecodename != N'Canceled' AND FilteredAppointment.owneridname IN (N'User1', N'User2', N'User3') GROUP BY FilteredAppointment.regardingobjectidname ,FilteredAppointment.owneridname ,FilteredAppointment.subject ,FilteredAppointment.scheduledstart ,FilteredAppointment.location ,FilteredCcx_member.ccx_mnemonic ORDER BY FilteredAppointment.regardingobjectidname
  </code></pre>
  
  <p>And my 2nd query would display one row only:</p>
  
  <pre><code>SELECT DISTINCT FilteredAppointment.regardingobjectidname ,FilteredAppointment.owneridname ,FilteredAppointment.subject ,FilteredAppointment.scheduledstart ,FilteredAppointment.location ,FilteredCcx_member.ccx_mnemonic FROM FilteredAppointment INNER JOIN FilteredAccount ON FilteredAppointment.regardingobjectid = FilteredAccount.accountid INNER JOIN FilteredCcx_member ON FilteredAccount.accountid = FilteredCcx_member.ccx_accountid WHERE FilteredAppointment.scheduledstart = (SELECT MAX(FilteredAppointment.scheduledstart) FROM FilteredAppointment WHERE FilteredAppointment.regardingobjectidname = FilteredAppointment.regardingobjectidname) AND  FilteredAppointment.statecodename != N'Canceled' AND FilteredAppointment.owneridname IN (N'User1', N'User2', N'User3') GROUP BY FilteredAppointment.regardingobjectidname ,FilteredAppointment.owneridname ,FilteredAppointment.subject ,FilteredAppointment.scheduledstart ,FilteredAppointment.location ,FilteredCcx_member.ccx_mnemonic ORDER BY FilteredAppointment.regardingobjectidname
  </code></pre>