Hi Please find below mytable structure and expected result. I have tried joining the same table with LastModified less than the other. But I still couldnt it in this order. Thanks in advance : /storage/temp/1165-untitled.png
Hello, I've had to make some guesses about keys etc. (it's helpful for future questions if you can post code to create the schema), but here goes: DECLARE @T TABLE ( RateCode CHAR(2) , Rate INT , LastModified DATETIME ) INSERT @T VALUES ( '01', 10, '2008-12-10 23:00' ), ( '01', 20, '2009-12-10 23:00' ), ( '01', 30, '2010-12-10 23:00' ) ;WITH LastModifiedDate ( RateCode, CurrentRate, PreviousModified ) AS ( SELECT a.RateCode , a.Rate CurrentRate , MAX(b.LastModified) AS PreviousModified FROM @T a LEFT JOIN @t b ON a.ratecode = b.ratecode AND a.LastModified > b.LastModified GROUP BY a.Rate , a.RateCode , a.LastModified ) SELECT LastModifiedDate.RateCode , LastModifiedDate.CurrentRate , z.Rate PreviousRate FROM LastModifiedDate LEFT JOIN @T z ON z.RateCode = LastModifiedDate.RateCode AND LastModifiedDate.PreviousModified = z.LastModified ORDER BY LastModifiedDate.CurrentRate DESC Basically, I'm calculating the latest modified date prior to the date of the current record, then using that to join back to the original table to get the rate, assuming that RateCode is your key.