MAX Date Value

[benchpolo]

I have a set of data that I need to select the maximum 'DateFrom' from a temp table. The script that I wrote somehow is still displaying both the minimum and maximum date. Please assist.

Thanks.

select a.MemberID
,a.LOINC
,a.LoinCDescr
,a.DateFrom
,a.Dateto
,a.ReferenceRange
,a.Result
from #CLAIMLAB a
inner join (select Memberid 
				,LOINC	
				,LoinCDescr
				,MAX(DateFrom) as DateFrom
				,Dateto
				,ReferenceRange
				,Result
			from #claimlab 
			group by memberid
				,LOINC	
				,LoinCDescr
				,DateFrom
				,Dateto
				,ReferenceRange
				,Result
			) a1 on a.Memberid = a1.memberid and a.datefrom=a1.DateFrom

[Rabbit]

It's going to return every date value. Not just min and max. It's because you're grouping by date from. Which means that the max for any date from will be itself.

[benchpolo]

Ok. I shortened the data set. It will me ID, DateFrom, Result. I need to display most recent date from. Somehow with the sql code below it is still displaying both records.

select memberid, MAX(convert(datetime,datefrom,101)) as datefrom , result as result 
from #a 
group by memberid, result

Result
1052	2012-03-13 00:00:00.000	64
1050	2012-01-03 00:00:00.000	74

Thanks.

[Rabbit]

Why wouldn't it show those two records? You're grouping by id and result. Which means it will show the max for each distinct id and result.