1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
/* generating table t ... */
with t as (
select 'B' code,to_date('04/05/2007','DD/MM/YYYY') dt,40 stock from dual union all
select 'C',to_date('06/03/2007','DD/MM/YYYY'),100 from dual union all
select 'A',to_date('10/01/2007','DD/MM/YYYY'),2 from dual union all
select 'A',to_date('31/12/2006','DD/MM/YYYY'),4 from dual union all
select 'B',to_date('04/11/2006','DD/MM/YYYY'),65 from dual union all
select 'A',to_date('25/08/2006','DD/MM/YYYY'),43 from dual union all
select 'D',to_date('19/02/2006','DD/MM/YYYY'),9 from dual union all
select 'D',to_date('27/04/2002','DD/MM/YYYY'),10 from dual
)
/* the query starts here */
select code,
max(dt) dt,
max(stock) keep (dense_rank last order by dt)
from t
where dt < date '2007-02-22'
group by code
;
C DT STOCK
- --------- ----------
A 10-JAN-07 2
B 04-NOV-06 65
D 19-FEB-06 9 |
