Faire un tri

[JeromeTheH]

Bonjour

Je suis débutant en oracle et je suis confronté à un soucis.
Je vous explique mon problème :
J'ai en base :

AGE  VILLE REGION NOM  PRENOM SEXE
---- ----- ------ ---- ------ ----
  12 LENS      62 Jim  Jam    H   
  13 LENS      62 Jim  Jam    H   
null LENS      62 John Doe    H   
  13 LENS      62 Jane Maj    F   

Mon objectif est d'avoir pour un couple unique ville<>région le nom et le prénom dont l'age est le plus grand
Pour l'exemple il faudrait ramener la 2nd ligne ( age 13 ) et la 4 ème.
Hors une 2ème condition vient s'ajouter : si il existe 2 entrées pour le champs SEXE, on sélectionne l'entrée dont la valeur est H.


J'imaginais créer une contrainte primary key sur ville et région mais mon sgbd refuse et je ne pense pas que ce soit la bonne méthode.
J'ai alors imaginé des requêtes pour gérer ce scénario sauf que je bute sur la gestion de la contrainte couple ville région.
Mon objectif est de selectionner seulement cette ligne :

AGE  VILLE REGION NOM  PRENOM SEXE
---- ----- ------ ---- ------ ----
  13 LENS      62 Jim  Jam    H   

Je suis sur oracle 11g.
Merci d'avance !

[Mohamed.Houri]

J'ai essayé

Code :

1
2
3
4
5
6
7
8
9
10
11
 
 
DROP TABLE t_ville purge;
 
CREATE TABLE t_ville (age number, ville varchar2(10), region number, nom varchar2(10), prenom varchar2(10), sexe varchar2(1));
 
INSERT INTO t_ville VALUES (12,  'LENS', 62, 'Jim','Jam','H');
INSERT INTO t_ville VALUES (13,  'LENS', 62, 'Jim','Jam','H');
INSERT INTO t_ville VALUES (NULL,'LENS', 62, 'John','Doe','H');
INSERT INTO t_ville VALUES (13,  'LENS', 62, 'Jane','Maj','F');
commit;

solution 1:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT
          min(nom) keep (dense_rank first ORDER BY age DESC nulls last, sexe DESC) nom 
        , min(prenom) keep (dense_rank first ORDER BY age DESC nulls last, sexe DESC) prenom 
        , ville                                                      
        , region
        , min(age) keep (dense_rank first  ORDER BY age DESC nulls last, sexe DESC) age
        , min(sexe) keep (dense_rank first ORDER BY age DESC nulls last, sexe DESC) sexe
    FROM   t_ville
    GROUP BY
          ville
        , region
    ;

solution 2:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SELECT 
     age
    ,nom
    ,prenom
    ,ville
    ,region
    ,sexe
FROM
    (SELECT 
          a.*
         ,dense_rank() over(partition BY ville, region ORDER BY age DESC nulls last, sexe DESC) rank
     FROM
         (SELECT
                 age
                ,nom
                ,prenom
                ,ville
                ,region
                ,sexe
          FROM t_ville
          ) a
      ) a
 WHERE a.rank = 1;

[Waldar]

Envoyé par JeromeTheH

J'imaginais créer une contrainte primary key sur ville et région mais mon sgbd refuse et je ne pense pas que ce soit la bonne méthode.

Je ne vois pas bien le lien entre une requête que vous essayez d'écrire et le positionnement d'une contrainte.
Cette dernière donne des règles aux données que vous insérez dans une table.
Votre besoin n'a rien à voir, vous avez déjà des données mais vous voulez seulement en sélectionner certaines.

Je le ferai ainsi :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH MaTable AS
(
SELECT   12 AS age, 'LENS' AS ville, 62 AS region, 'Jim' AS nom, 'Jam' AS prenom, 'H' AS sexe FROM dual union ALL
SELECT   13       , 'LENS'         , 62          , 'Jim'       , 'Jam'          , 'H'         FROM dual union ALL  
SELECT NULL       , 'LENS'         , 62          , 'John'      , 'Doe'          , 'H'         FROM dual union ALL
SELECT   13       , 'LENS'         , 62          , 'Jane'      , 'Maj'          , 'F'         FROM dual
)
  ,  sr AS
(
SELECT age, ville, region, nom, prenom, sexe
     , row_number() over(partition BY ville, region ORDER BY age DESC nulls last, sexe DESC) AS rn
  FROM MaTable
)
SELECT age, ville, region, nom, prenom, sexe
  FROM sr
 WHERE rn = 1;
 
AGE  VILLE REGION NOM  PRENOM SEXE
---- ----- ------ ---- ------ ----  
  13 LENS      62 Jim  Jam    H

@Mohamed.Houri, on peut raccourcir légèrement votre première solution :

Code :

1
2
3
4
5
6
7
8
9
  SELECT max(age)                                                                    AS age
       , ville                                                      
       , region
       , min(nom)    keep (dense_rank first ORDER BY age DESC nulls last, sexe DESC) AS nom 
       , min(prenom) keep (dense_rank first ORDER BY age DESC nulls last, sexe DESC) AS prenom 
       , max(sexe)   keep (dense_rank first ORDER BY age DESC nulls last, sexe DESC) AS sexe
    FROM t_ville
GROUP BY ville
       , region;