SQLTeam.com | Weblogs | Forums

Getting Duplicates Still with Max


#1

Hello!

I am using the following query, but am still getting dupes.

Select va.LoanNumber,Max(va.CreateDate),va.LoanStatus,va.Source,va.LoanOfficerName
From VehicleApplication va
Where va.AppReceiveDate Between '10/01/2017' AND '12/31/2018' AND va.loanStatus In ('APP','AD', 'DEC', 'OFF', 'AA')
Group By va.LoanNumber, va.LoanStatus,va.Source,va.LoanOfficerName
Order By va.LoanNumber

Where am I going wrong?
Thanks.


#2

does .CreateDate have time stamp on it? as in 2018-12-05 15:30:39.560


#3

Please post DDL and inserts with sample data.


#4
select va.LoanNumber,va.CreateDate,va.LoanStatus,va.Source,va.LoanOfficerName
from (
    Select *, row_number() over(partition by LoadNumber order by CreateDate desc) as row_num
    From VehicleApplication
) as va
where va.row_num = 1
Order By va.LoanNumber

#5

It worked!!!! Thanks ScottPletcher.

yosiasz ... yes it has a time stamp on it.