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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
|
SQL> SELECT numcompte, dateval, valeur
2 FROM (SELECT numcompte, dateval, valeur,
3 ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval) rn_min,
4 ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval DESC)rn_max
5 FROM matable)
6 WHERE rn_min = 1 OR rn_max = 1
7 /
NUMCOMPTE DATEVAL VALEUR
-------------------- ---------- ----------
007 2009-05-08 10
007 2009-07-07 53
008 2009-05-18 43
008 2009-07-07 5
Ecoulé : 00 :00 :07.68
SQL> SELECT *
2 FROM matable a
3 WHERE EXISTS (SELECT 1
4 FROM matable b
5 WHERE a.numcompte = b.numcompte
6 HAVING a.dateval IN (MAX (b.dateval), MIN (b.dateval)))
7 /
NUMCOMPTE DATEVAL VALEUR
-------------------- ---------- ----------
007 2009-05-08 10
007 2009-07-07 53
008 2009-05-18 43
008 2009-07-07 5
Ecoulé : 00 :00 :03.17
SQL> SELECT a.numcompte, MIN (a.dateval) dateval,
2 MIN (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval) valeur
3 FROM matable a
4 GROUP BY a.numcompte
5 UNION
6 SELECT a.numcompte, MAX (a.dateval) dateval,
7 MAX (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval DESC) valeur
8 FROM matable a
9 GROUP BY a.numcompte;
NUMCOMPTE DATEVAL VALEUR
-------------------- ---------- ----------
007 2009-05-08 10
007 2009-07-07 53
008 2009-05-18 43
008 2009-07-07 5
Ecoulé : 00 :00 :01.34
SQL> WITH pivot AS (
2 SELECT level x FROM dual connect BY level <= 2
3 )
4 SELECT numcompte,
5 Case When x = 1 Then dmin Else Dmax End AS Dat,
6 Case When x = 1 Then valmin Else Valmax End AS Valuer
7 FROM (
8 SELECT numcompte, min(dateval) dmin, min(valeur) KEEP (DENSE_RANK FIRST ORDER BY dat
eval) valmin,
9 max(dateval) dmax, max(valeur) KEEP (DENSE_RANK LAST ORDER BY dateval) valma
x
10 FROM matable
11 GROUP BY numcompte
12 ), pivot
13 ORDER BY numcompte, x;
NUMCOMPTE DAT VALUER
-------------------- ---------- ----------
007 2009-05-08 10
007 2009-07-07 53
008 2009-05-18 43
008 2009-07-07 5
Ecoulé : 00 :00 :01.21
SQL> |