Hi ,

Try this script

Declare @t table(id int, amount varchar(10),adate datetime)

insert into @t select 1,'120', dateadd(day,-1,getdate())

insert into @t select 2,'121', getdate()

insert into @t select 1,'122', dateadd(day,-2,getdate())

insert into @t select 4,'123', getdate()

insert into @t select 1,'124', getdate()

insert into @t select 3,'125', dateadd(day,-3,getdate())

insert into @t select 3,'126', getdate()

--------HERE

;WITH cte AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY adate desc) AS ROW,

id,

amount,

adate

FROM @t

)

SELECT *

FROM cte

where Row = 1

Thanks,