Hi guys, I'm getting duplicates from the below query. I want to retrieve only the most recent row according to the field 'lastmodified' from the table MNI_MUG.
SELECT a.mugid as MugRecordIdentifier,a.ecsoid as PersonIdentifier,a.lastmodified,
Demdatatype = CASE WHEN a.age > 17 THEN 'A' ELSE 'J' END, a.Race, a.Height,
b.lname as Lastname, b.fname as Firstname, a.sex as Gender, a.Dob, a.hair as HairColor,
a.eyes as EyeColor, a.weight, image01 as FrontalPortrait, convert(date,a.[date]) as ImageDate
FROM MNI_MUG A
INNER JOIN MNI B ON a.ecsoid = b.ecsoid
WHERE a.image_type = 'F' and a.ecsoid IS NOT NULL AND convert(date,a.lastmodified) > '8/14/2017'
AND convert(date, a.lastmodified) < '8/16/2017'
Your help is greatly appreciated. Thank you.