Re: Query to get Most Recent records
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Sreejith.Sreekantan@xxxxxxxxxx
- Date: Mon, 25 Oct 2010 11:16:59 +0200
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 <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
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."
>
>
>
>
Other related posts:
