Hi , Thanks for the hint. Just a small question and clarification. The table in the example is just a sample table( I am not storing age) I cannot do an equality on records after selecting the most recent time stamp, since all the five records created are on different timestamp. I think I was not very clear on my previous mail, I would like to see the data - not for the record with most recent date, but with the changed value over the period of time. For eg: In my example, if you check the address , it got changed on 2nd and 5th and 6th. Age also got changed on 3rd and 4th. I would like to get the latest value for each column. In this case the latest value may not be the one stored against most recenct timestamp . Hope I made the situation clear now. Thank You, Kind Regards, Sreejith Nair From: Stephane Faroult <sfaroult@xxxxxxxxxxxx> To: Sreejith.Sreekantan@xxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Date: 10/25/2010 02:46 PM Subject: Re: Query to get Most Recent records Yes, it is possible. Hint : first return for each person the most recent timestamp (you are on good tracks when you mention analytic functions) with the remainder of the data, then select the rows for which the timestamp is equal to this most recent timestamp. There are otherways to do it, you can also number the records per person in decreasing order of timestamp and only return those with number 1, for instance. Storing the age is a bad idea. Birth dates need fewer updates and allows you to compute the age. HTH Stephane Faroult RoughSea Ltd Konagora RoughSea Channel on Youtube On 10/25/2010 11:07 AM, Sreejith S Nair wrote: Hi listmembers, I am trying to write a query using analytic functions to get a query result as follows PERS_ID AGE SEX ADDRESS TMSTMP 1 12 M TVM 1/1/2010 1 EKM 2/1/2010 1 13 3/1/2010 1 14 4/1/2010 1 BOM 5/1/2010 1 LHR 6/1/2010 2 12 F TVM 1/1/2010 2 LHR 2/1/2010 2 13 3/1/2010 2 14 4/1/2010 2 PUR 5/1/2010 2 DEL 6/1/2010 The concept is that the initial record, with all columns populated(record created on 1/1/2010) will get changed over a time period, and changed values are stored in this fashion. I would like to retrieve the latest value of records for this person / a record which is less than a date, say 6/1/2010. I would like to see the records as PERS_ID AGE SEX ADDRESS TMSTMP 1 14 M BOM 5/1/2010 2 14 F PUR 5/1/2010 Will it be possible to write these as a single query ? Please advice Thank You, Kind Regards, Sreejith Nair DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."