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 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL>
SQL> With Data As (
2 Select 1 As Num, 'Alain' As Nom, '0633100000' As Tel, 'paris' As Adresse, '01/02/2011' As Dat_maj from dual union all
3 Select 2, 'Alain', '0633100000', 'marseille', '02/02/2011' from dual union all
4 Select 3, 'Marc', '0633100010', 'paris', '10/02/2011' from dual union all
5 Select 4, 'Jully', '0633100010', 'paris', '15/02/2011' from dual union all
6 Select 5, 'Brad', '0633100000', 'lyon', '20/02/2011' from dual union all
7 Select 6, 'Michel', '0633100010', 'madrid', '25/02/2011' from dual
8 )
9 Select Max(Num) Keep (Dense_Rank Last Order By Dat_Maj) As Num,
10 Max(Nom) Keep (Dense_Rank Last Order By Dat_Maj) As Nom,
11 Tel,
12 Max(Adresse) Keep (Dense_Rank Last Order By Dat_Maj) As Adresse,
13 Max(Dat_Maj) Keep (Dense_Rank Last Order By Dat_Maj) As Dat_Maj
14 From data
15 Where dat_maj < &p_dat
16 Group By Tel
17 /
NUM NOM TEL ADRESSE DAT_MAJ
---------- ------ ---------- --------- ----------
5 Brad 0633100000 lyon 20/02/2011
6 Michel 0633100010 madrid 25/02/2011
SQL> |
Partager