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
| SQL> with test (PERSONNE , CHAMP , VALEUR , DATE_MODIFICATION) as (
2 select 'toto', '01', '10', to_date('01/01/2010','dd/mm/yyyy') from dual union all
3 select 'toto', '01', '12', to_date('02/01/2010','dd/mm/yyyy') from dual union all
4 select 'toto', '01', '20', to_date('03/01/2010','dd/mm/yyyy') from dual union all
5 select 'toto', '02', '01', to_date('01/01/2010','dd/mm/yyyy') from dual union all
6 select 'toto', '02', '02', to_date('02/01/2010','dd/mm/yyyy') from dual union all
7 select 'toto', '03', '21', to_date('02/01/2010','dd/mm/yyyy') from dual union all
8 select 'toto', '03', '22', to_date('04/01/2010','dd/mm/yyyy') from dual union all
9 select 'toto', '03', '28', to_date('05/01/2010','dd/mm/yyyy') from dual union all
10 select 'toto', '04', '48', to_date('01/01/2010','dd/mm/yyyy') from dual
11 )
12 select personne, champ,
13 max(valeur) keep (dense_rank first order by date_modification desc) as max_val,
14 max(date_modification)
15 from test
16 group by personne, champ
17 /
PERS CH MA MAX(DATE
---- -- -- --------
toto 01 20 03/01/10
toto 02 02 02/01/10
toto 03 28 05/01/10
toto 04 48 01/01/10
SQL> |
Partager