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 27 28 29
|
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
SQL>
SQL> With Data As (
2 Select 'ID1' As id, 'DIS' as statut, to_date('23/05/2015','DD/MM/YYYY') As dat_sta from dual union all
3 Select 'ID1' As id, 'BLO' as statut, to_date('24/05/2015','DD/MM/YYYY') As dat_sta from dual union all
4 Select 'ID1' As id, 'DIS' as statut, to_date('25/05/2015','DD/MM/YYYY') As dat_sta from dual union all
5 Select 'ID1' As id, 'BLO' as statut, to_date('26/05/2015','DD/MM/YYYY') As dat_sta from dual union all
6 Select 'ID1' As id, 'DIS' as statut, to_date('27/05/2015','DD/MM/YYYY') As dat_sta from dual union all
7 Select 'ID2' As id, 'DIS' as statut, to_date('23/05/2015','DD/MM/YYYY') As dat_sta from dual union all
8 Select 'ID2' As id, 'BLO' as statut, to_date('24/05/2015','DD/MM/YYYY') As dat_sta from dual union all
9 Select 'ID2' As id, 'DIS' as statut, to_date('25/05/2015','DD/MM/YYYY') As dat_sta from dual union all
10 Select 'ID2' As id, 'BLO' as statut, to_date('26/05/2015','DD/MM/YYYY') As dat_sta from dual union all
11 Select 'ID2' As id, 'DLS' as statut, to_date('27/05/2015','DD/MM/YYYY') As dat_sta from dual
12 )
13 Select id,
14 Max(statut) Keep (Dense_Rank Last Order By dat_sta) statut,
15 Max(dat_sta) Keep (Dense_Rank Last Order By dat_sta) dat_sta
16 From Data
17 Where statut != 'DLS'
18 Group By id
19 Having Max(statut) Keep (Dense_Rank Last Order By dat_sta) = 'BLO'
20 /
ID STATUT DAT_STA
--- ------ -----------
ID2 BLO 26/05/2015
SQL> |
Partager