MSSQL result return with doublet result, need other way than distinct

WeTi
WeTi used Ask the Experts™
on
Dear expert

Please help me with this MSSQL Query:

SELECT FirstName LastName DatetimeCancel
FROM [Prod].[System].[Approvement] aa 
join [Prod].[Contact].[Individual] ci on aa.PersonalIdentityNumberSender = ci.PersonalIdentityNumber join [Prod].[Contact].[Address] ca 
on ci.Id = ca.Id join [Prod].[Subscription].[Member] rsm on rsm.IdMember = ca.Id join [Prod].[Invoice].[Receiver] ser on ser.Id = ca.Id
where ApprovementStatusId = 7 and aa.DatetimeCancel is not null and aa.Id not in (select id from [Prod].[System].[Approvement] where ApprovementStatusId = 5 or ApprovementStatusId = 3 or ApprovementStatusId = 4 or ApprovementStatusId = 6 or ApprovementStatusId = 8 or ApprovementStatusId = 9 or ApprovementStatusId = 10 or ApprovementStatusId = 11 or ApprovementStatusId = 12 or ApprovementStatusId = 13 )

Open in new window


Result return with doublets because DatetimeCancel is diffirent, now I want only show the lastest time as result, in this case 2018-05-25 others ignore. Distinct can only random remove one (or more) doublets.
Is there a way to do this?
SC.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Simple MAX Aggregate function would do..
SELECT FirstName, LastName, MAX(DatetimeCancel) DatetimeCancel
FROM [Prod].[System].[Approvement] aa 
join [Prod].[Contact].[Individual] ci on aa.PersonalIdentityNumberSender = ci.PersonalIdentityNumber join [Prod].[Contact].[Address] ca 
on ci.Id = ca.Id join [Prod].[Subscription].[Member] rsm on rsm.IdMember = ca.Id join [Prod].[Invoice].[Receiver] ser on ser.Id = ca.Id
where ApprovementStatusId = 7 and aa.DatetimeCancel is not null and aa.Id not in (select id from [Prod].[System].[Approvement] where ApprovementStatusId = 5 or ApprovementStatusId = 3 or ApprovementStatusId = 4 or ApprovementStatusId = 6 or ApprovementStatusId = 8 or ApprovementStatusId = 9 or ApprovementStatusId = 10 or ApprovementStatusId = 11 or ApprovementStatusId = 12 or ApprovementStatusId = 13 )
GROUP BY FirstName, LastName

Open in new window

Author

Commented:
Yes Max and group by helped thanks alot
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to assist!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial