Comparing dates

[Elamranii]

Hi,
I have this table where I am trying to compare ChgDt.
I want to see a record that is higher than the one on top of it.

example: the only one I want to see in the case below is srn3 because its ChgDt is higher than srno2 ChgDt.

srno1 ChgDt is higher than 2 (Skip)
srno2 ChgDt is lower than 3 (I want to see this record)
srno4 ChgDt is hihger than 5 (Skip)
etc....
Is there a formula I can use. Thanks in advance.
Ismail

 

[Remou]

Hi
Have you looked at the DMax function?

 

[Elamranii]

No, I'll try it.
Thanks

 

[Elamranii]

This is the code I used and it worked. Thanks for your help. Ismail

SELECT A.mbrrecid, A.srno, A.ChgDt, B.ChgDt
FROM 1 AS A INNER JOIN 1 AS B ON A.mbrrecid = B.mbrrecid AND A.srno = B.srno+1
WHERE A.ChgDt > B.ChgDt;