How to display distinct values based on MAX date in report builder?

Question

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.

This is the data:

Raw Data

And this is what I'm looking to achieve:

Desired Data

I tried to write 2 queries but unfortunately:

My 1st query would display duplicated rows for each company:

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

And my 2nd query would display one row only:

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

Answer 1

I managed to solve the issue - Thank you for the help again!

WITH apptmts AS (SELECT TOP 1 WITH TIES fa.scheduledstart,fa.location,fa.regardingobjectidname,mem.ccx_mnemonic,fa.owneridname,fa.subject FROM FilteredAppointment fa JOIN FilteredAccount acc on fa.regardingobjectid = acc.accountid JOIN FilteredCcx_member mem ON acc.accountid = mem.ccx_accountid WHERE fa.statecodename != N'Canceled' AND fa.owneridname IN (N'User1', N'User2', N'User3') ORDER BY ROW_NUMBER() OVER(PARTITION BY fa.regardingobjectidname ORDER BY fa.scheduledstart DESC) ) SELECT * FROM apptmts ORDER BY scheduledstart DESC