Get max value and max date from sql query-CodePudding

I have a table with duplicate member names but these duplicates also have more than one date and a specific ID. I want the row of the member name with the most recent date (because a member could have been called more than one time a day) and biggest CallID number.

MemberID    FirstName     LastName     CallDate     CallID
 0123         Carl         Jones      2019-03-01    123456
 0123         Carl         Jones      2020-10-12    215789
 0123         Carl         Jones      2020-10-12    312546
 2045        Sarah         Marty      2021-05-09    387945
 2045        Sarah         Marty      2021-08-11    398712
 4025         Jane         Smith      2021-10-18    754662
 4025         Jane         Smith      2021-11-03    761063
 8282         Suzy         Aaron      2019-12-12    443355
 8282         Suzy         Aaron      2019-12-12    443386

So the desired output from this table would be

MemberID    FirstName     LastName     CallDate     CallID
 0123         Carl         Jones      2020-10-12    312546
 2045        Sarah         Marty      2021-08-11    398712
 4025         Jane         Smith      2021-11-03    761063
 8282         Suzy         Aaron      2019-12-12    443386

The query I've tried is

SELECT DISTINCT MemberID, FirstName, LastName, MAX(CallDate) as CallDate, MAX(CallID) as CallID
FROM dbo.table 
GROUP BY MemberID, FirstName, LastName, CallDate, CallID 
ORDER BY LastName asc;

But I'm still getting duplicate names with all their calldates and CallID

CodePudding user response：

try removing CallDate, CallID from the group by clause.

So :

SELECT MemberID, FirstName, LastName, MAX(CallDate) as CallDate, MAX(CallID) as CallID

FROM dbo.table 
GROUP BY MemberID, FirstName, LastName 

ORDER BY LastName asc;

Hopefully that should do it.

CodePudding user response：

you can use window function:

select * from (
   select * , row_number() over (partition by MemberID order by CallID desc) rn
   from tablename
) t where rn = 1