Join to MAX(date) row

Transact-SQL
1

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.

use mni
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.

2

As below, assuming that "lastmodified" is a date/datetime/datetime2 and not char/varchar. If that column is char/varchar, you will have to leave the CONVERT, otherwise you are far better off without it.

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 (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ecsoid ORDER BY lastmodified DESC) AS row_num
    FROM MNI_MUG
    WHERE image_type = 'F' AND
        ecsoid IS NOT NULL AND
        lastmodified >= '20170814' AND lastmodified < '20170817'
) AS A
INNER JOIN MNI B ON a.ecsoid = b.ecsoid
WHERE a.row_num = 1
3

Here's another option...

USE mni;
GO 
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 
	dbo.MNI B 
	CROSS APPLY (
		SELECT TOP 1
			*
		FROM	
			dbo.MNI_MUG M
		WHERE 
			B.ecsoid = M.ecsoid
			AND M.image_type = 'F'
			AND M.lastmodified >= '2017-08-14'
			AND M.lastmodified < '2017-08-17'
		ORDER BY
			M.lastmodified DESC
		) A;