Re: Query to get Most Recent records
- From: Maxim Demenko <mdemenko@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 25 Oct 2010 11:45:08 +0200
One possible way could be
SQL> with t as (
2 select 1 PERS_ID,12 AGE,'M' SEX,'TVM'
ADDRESS,to_date('01.01.2010','dd.mm.yyyy') TMSTMP from dual union all
3 select 1,null,null,'EKM',to_date('02.01.2010','dd.mm.yyyy') from
dual union all
4 select 1,13,null,null,to_date('03.01.2010','dd.mm.yyyy') from
dual union all
5 select 1,14,null,null,to_date('04.01.2010','dd.mm.yyyy') from
dual union all
6 select 1,null,null,'BOM',to_date('05.01.2010','dd.mm.yyyy') from
dual union all
7 select 1,null,null,'LHR',to_date('06.01.2010','dd.mm.yyyy') from
dual union all
8 select 2,12,'F','TVM',to_date('01.01.2010','dd.mm.yyyy') from
dual union all
9 select 2,null,null,'LHR',to_date('02.01.2010','dd.mm.yyyy') from
dual union all
10 select 2,13,null,null,to_date('03.01.2010','dd.mm.yyyy') from
dual union all
11 select 2,14,null,null,to_date('04.01.2010','dd.mm.yyyy') from
dual union all
12 select 2,null,null,'PUR',to_date('05.01.2010','dd.mm.yyyy') from
dual union all
13 select 2,null,null,'DEL',to_date('06.01.2010','dd.mm.yyyy') from dual
14 )
15 -- End test data
16 select
17 pers_id,
18 max(age) keep(dense_rank last order by tmstmp) age,
19 max(sex) keep(dense_rank last order by tmstmp) sex,
20 max(address) keep(dense_rank last order by tmstmp) address,
21 max(tmstmp) keep(dense_rank last order by tmstmp) tmstmp
22 from (
23 select pers_id,
24 last_value(age ignore nulls) over(partition by pers_id order by
tmstmp) age,
25 last_value(sex ignore nulls) over(partition by pers_id order by
tmstmp) sex,
26 last_value(address ignore nulls) over(partition by pers_id order
by tmstmp) address,
27 tmstmp
28 from t where tmstmp < to_date('06.01.2010','dd.mm.yyyy')
29 )
30 group by pers_id
31 /
PERS_ID AGE S ADD TMSTMP
---------- ---------- - --- ------------------
1 14 M BOM 05-JAN-10
2 14 F PUR 05-JAN-10
Best regards
Maxim
On 25.10.2010 11:07, 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:
