Re: Query to get Most Recent records

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."

Other related posts: