group by max expiration date.

[sapator]

Hi.

SELECT  memberid, ExpirationDate, '{"Inv":"' + cast(IsInvoiced as nvarchar(1)) +'","Marc":"' + cast(AcceptMarketing as nvarchar(1)) +'","FtDate": "'+  convert(nvarchar(20),RegistrationDate,112) +' ' + convert(nvarchar(20),RegistrationDate,114) +'"}' as jsoncol 
FROM FamilyClubRegistration where paiddate is not null and cardnumber is not null and MemberId is not null and expirationdate is not null and MemberId in ( '1', '2','3')
and (ExpirationDate) IN (
    SELECT  MAX(ExpirationDate)
    FROM FamilyClubRegistration
    GROUP BY memberid
)

What I need to do is only have a row per member per max expiration date.
The above will give me 2 correct rows and one duplicate, because an expiration date happens to be the same. I did not expected that to occur but clearly some burthead manually inserted the row and they are the same now.
So is there another way to go about?

Imagine for example,
Member 1 has
memberid, expirationdate
1,'20230505 12:12:12'
1,'20230506 12:11:11'

and somewhere another member has
memberid, expirationdate
33, '20230506 12:11:11'

So I expect double rows there.

Thanks.

[sapator]

Hmm.
I think this?

SELECT  memberid, '{"Inv":"' + cast(IsInvoiced as nvarchar(1)) +'","Marc":"' + max(cast(AcceptMarketing as nvarchar(1)) +'","FtDate": "'+  convert(nvarchar(20),RegistrationDate,112) +' ' + convert(nvarchar(20),RegistrationDate,114) +'"}') as jsoncol
FROM FamilyClubRegistration where paiddate is not null and cardnumber is not null and MemberId is not null and expirationdate is not null and MemberId in ( 'VCNWS05NPKQG', 'VCBPN4RC83G5','VC13P00NCJGN')
and (ExpirationDate) IN (
    SELECT  MAX(ExpirationDate)
    FROM FamilyClubRegistration
    GROUP BY memberid
)
group by memberid,IsInvoiced,AcceptMarketing