Discussion :

[jbat]

Bonjour,

J'ai une table de projet versionnés qui se présente sous la forme suivante :
TR_PROJET
- PRO_ID_K (clé primaire auto incrémentale)
- PRO_DESIGNATION (nom du projet)
- PRO_VERSION_MAJEUR
- PRO_VERSION_MINEUR

Les données sont donc sous la forme :
| 1 | Parking | 1 | 1 |
| 2 | Parking | 1 | 2 |
| 3 | Parking | 2 | 2 | <- Dernière version du 1er projet.
| 4 | Parking | 1 | 3 |
| 5 | Parking | 2 | 1 |
| 6 | Cinéma | 1 | 1 |
| 7 | Cinéma | 1 | 2 | <- Dernière version du 2nd projet.


Je cherche désespéremment à obtenir une liste des dernières versions par projet du genre :
| 3 | Parking | 2 | 2 | <- Dernière version du 1er projet.
| 7 | Cinéma | 1 | 2 | <- Dernière version du 2nd projet.

Vous remarquerez que la dernière version d'un projet n'est pas nécessairement celle qui a l'ID le plus élevé.

Est-il possible d'obtenir un tel résultat avec une requette sur ma table telle qu'elle est structurée ?

Toute aide sera la bienvenue

[tatayo]

Bonjour,
Je dirai qu'ici il ne faut pas faire de regroupement (à cause de la version mineure), mais plutôt de chercher "la dernière version", i.e. celle pour laquelle il n'en existe pas de plus importante.
Ca peut ce faire avec un NOT EXIST, une jointure externe...

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select t1.*
from LaTable t1
left outer join LaTable t2 on t1.version <= t2.version and t1.col1 = t2.col1
where t2.id is null

Ici il faut faire la comparaison sur les deux numéros de version, et la jointure sur la désignation*.

Tatayo

P.S. je trouve que c'est une très mauvaise idée d'utiliser le nom du projet comme clé...

[islamov2000]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
with test as (
select  1 PRO_ID_K, 'Parking' PRO_DESIGNATION, 1 PRO_VERSION_MAJEUR, 1 PRO_VERSION_MINEUR from dual union
select  2 , 'Parking' , 1 , 2 from dual union
select  3 , 'Parking' , 2 , 2 from dual union
select  4 , 'Parking' , 1 , 3 from dual union
select  5 , 'Parking' , 2 , 1 from dual union
select  6 , 'Cinéma' , 1 , 1 from dual union
select  7 , 'Cinéma' , 1 , 2 from dual )
 
 
select t.* from test t
where t.PRO_DESIGNATION =(select PRO_DESIGNATION from test
                          group by PRO_DESIGNATION
                          having max(PRO_VERSION_MAJEUR||PRO_VERSION_MINEUR) =t.PRO_VERSION_MAJEUR||t.PRO_VERSION_MINEUR)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
  PRO_ID_K    PRO_DESIGNATION    PRO_VERSION_MAJEUR    PRO_VERSION_MINEUR
  3                 Parking                2                      2
  7                 Cinéma                1                      2

Bonne chance.

[al1_24]

Le principe est d'appliquer deux fois la sélection sur la version la plus élevée, une première fois sur la version majeure, une seconde sur la version mineure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT  det.pro_id_k
    ,   det.pro_designation
    ,   det.pro_version_majeur
    ,   det.pro_version_mineur
FROM    tr_projet   det
WHERE   EXISTS
        (   SELECT  NULL
            FROM    tr_projet   ni2
            WHERE   det.pro_designation     = ni2.pro_designation
                AND det.pro_version_majeur  = ni2.pro_version_majeur
                AND EXISTS
                    (   SELECT  NULL
                        FROM    tr_projet   ni1
                        WHERE   ni2.pro_designation     = ni1.pro_designation
                        HAVING  ni2.pro_version_majeur  = MAX(ni1.pro_version_majeur)
                    )
            HAVING  det.pro_version_mineur  = MAX(ni2.pro_version_mineur)
        )
;

Cela peut aussi se faire avec les fonctions de regroupement analytique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  det.pro_id_k
    ,   det.pro_designation
    ,   det.pro_version_majeur
    ,   det.pro_version_mineur
FROM    tr_projet   det
    INNER JOIN
        (   SELECT  sel.pro_id_k
                ,   RANK() OVER(PARTITION BY sel.pro_designation 
                        ORDER BY sel.pro_version_majeur DESC, sel.pro_version_mineur DESC) AS rang
            FROM    tr_projet   sel
        )   niv
        ON  sel.pro_id_k    = niv.pro_id_k
WHERE   niv.rang    = 1
;

Mais il y a encore plein d'autres manières de le faire

[tatayo]

islamov2000 => avec une concaténation, les versions 1.12 et 11.2 deviennent équivalentes...

Tatayo.

[al1_24]

Attention, dans la version d'islamov2000 il y a un petit aménagement à faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
...
having max(PRO_VERSION_MAJEUR || '|' || PRO_VERSION_MINEUR) =t.PRO_VERSION_MAJEUR || '|' || t.PRO_VERSION_MINEUR)

Sans séparateur, la version 1.23 serait au même niveau que la version 12.3

edit : grillé par tatayo

[punkoff]

Cela peut aussi se faire avec les fonctions de regroupement analytique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  det.pro_id_k
    ,   det.pro_designation
    ,   det.pro_version_majeur
    ,   det.pro_version_mineur
FROM    tr_projet   det
    INNER JOIN
        (   SELECT  sel.pro_id_k
                ,   RANK() OVER(PARTITION BY sel.pro_designation 
                        ORDER BY sel.pro_version_majeur DESC, sel.pro_version_mineur DESC) AS rang
            FROM    tr_projet   sel
        )   niv
        ON  sel.pro_id_k    = niv.pro_id_k
WHERE   niv.rang    = 1
;

Mais il y a encore plein d'autres manières de le faire

Y a une jointure en trop

[jbat]

Bonjour à tous,

Merci pour votre réactivité. Je ne pensais pas avoir tant de réponses, et surtout pas si vite.

Je ne vais pas pouvoir tester vos solutions dès à présent car j'y travaille sur mon temps perso et... ben là, je suis au travail

Je vais tenter aussi d'extrapoler vos réponses car j'avais un peu simplifié le problèmes et en vérité mon numéro de version est sur 4 colonnes et non pas deux.

En tout cas, merci, je reviendrai ce soir ou demain pour signaler si le problème est résolu ou on

[islamov2000]

tatayo pour ta remarque.

@al1_24, même avec un séparateur ne marche pas, car "2,2" est grande que "11.2" par contre 11.2 est plus grande que 2.2.
Pour ce faire j'ai amélioré la requête.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH test AS (
SELECT  1 PRO_ID_K, 'Parking' PRO_DESIGNATION, 1 PRO_VERSION_MAJEUR, 1 PRO_VERSION_MINEUR FROM dual union
SELECT  2 , 'Parking' , 11 , 2 FROM dual union
SELECT  3 , 'Parking' , 2 , 2 FROM dual union
SELECT  4 , 'Parking' , 1 , 3 FROM dual union
SELECT  5 , 'Parking' , 2 , 1 FROM dual union
SELECT  6 , 'Cinéma' , 1 , 1 FROM dual union
SELECT  7 , 'Cinéma' , 1 , 2 FROM dual )
 
 
SELECT t.* FROM test t
WHERE t.PRO_DESIGNATION =(SELECT PRO_DESIGNATION FROM test
                          GROUP BY PRO_DESIGNATION
                          HAVING max(to_number(PRO_VERSION_MAJEUR||','||PRO_VERSION_MINEUR)) =to_number(t.PRO_VERSION_MAJEUR||','||t.PRO_VERSION_MINEUR))

[islamov2000]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  det.pro_id_k
    ,   det.pro_designation
    ,   det.pro_version_majeur
    ,   det.pro_version_mineur
FROM    tr_projet   det
    INNER JOIN
        (   SELECT  sel.pro_id_k
                ,   RANK() OVER(PARTITION BY sel.pro_designation 
                        ORDER BY sel.pro_version_majeur DESC, sel.pro_version_mineur DESC) AS rang
            FROM    tr_projet   sel
        )   niv
        ON  sel.pro_id_k    = niv.pro_id_k
WHERE   niv.rang    = 1
;

Mais il y a encore plein d'autres manières de le faire

il y a une erreur dans la jointure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 ON  sel.pro_id_k    = niv.pro_id_k

il faut la corriger en mettant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 ON  det.pro_id_k    = niv.pro_id_k

et ça marche.

[Waldar]

jbat, pensez à indiquer le SGBD que vous utilisez.

Tous les SGBD n'ont pas le même niveau de SQL, et les solutions techniques qui vous ont été présentées ici peuvent ou ne peuvent pas fonctionner.

[J1]

Bonsoir,

la solution la plus simple qui me vienne à l'esprit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT *
FROM TR_PROJET P
WHERE NOT EXISTS (
	SELECT 1
	FROM TR_PROJET
	WHERE PRO_DESIGNATION=P.PRO_DESIGNATION
	AND (PRO_VERSION_MAJEUR>P.PRO_VERSION_MAJEUR 
		OR PRO_VERSION_MAJEUR=P.PRO_VERSION_MAJEUR AND PRO_VERSION_MINEUR>P.PRO_VERSION_MINEUR));

[jbat]

Bonjour,

Je suis toujours en train d'adapter vos réponses à mon besoin précis (que j'avais simplifié (ma version est sur 4 colonnes et non 2)). Et puis mes compétances en SQL ne sont pas au niveau que j'aimerai . De plus, je cherche avant tout à comprendre vos propositions que je n'aimerai pas appliquer bêtement

Pour info, je suis sur MySql.

Merci de votre aide, je continue sur le sujet et vous tiendrai informé.

[jbat]

Après quelques tentatives d'application de vos propositions, la conclusion, c'est que pour moi, dès qu'on sort des requettes simples, SQL c'est un peu de la magie noire.

La 1ère proposition d' al1_24 me semblais prometteuse... mais je ne comprends pas bien comment fonctionnent les "EXISTS"...

Je n'arrive donc pas à gérer la version sur 4 colonnes au lieu de 2.

Je m'explique. Voici ma structure de données.
TR_PROJET
- PRO_ID_K (clé primaire auto incrémentale)
- PRO_DESIGNATION (nom du projet)
- PRO_VERSION_MAJEUR
- PRO_VERSION_MINEUR
- PRO_VERSION_RELEASE
- PRO_VERSION_BUILD

Les données sont donc sous la forme :
| 1 | Parking | 1 | 1 | 14 | 12 |
| 2 | Parking | 1 | 2 | 19 | 33 |
| 3 | Parking | 2 | 2 | 18 | 27 | <- Dernière version du 1er projet.
| 4 | Parking | 1 | 3 | 11 | 78 |
| 5 | Parking | 2 | 1 | 19 | 23 |
| 6 | Cinéma | 1 | 1 | 23 | 38 |
| 7 | Cinéma | 1 | 2 | 34 | 98 | <- Dernière version du 2nd projet.


Quelqu'un a la solution à mon problème ?

[J1]

Bonjour,

la solution que je te proposais précédemment, mais cette fois-ci sur tes 4 colonnes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM TR_PROJET P
WHERE NOT EXISTS (
	SELECT 1
	FROM TR_PROJET
	WHERE PRO_DESIGNATION=P.PRO_DESIGNATION
	AND (PRO_VERSION_MAJEUR>P.PRO_VERSION_MAJEUR 
		OR PRO_VERSION_MAJEUR=P.PRO_VERSION_MAJEUR AND PRO_VERSION_MINEUR>P.PRO_VERSION_MINEUR
		OR PRO_VERSION_MAJEUR=P.PRO_VERSION_MAJEUR AND PRO_VERSION_MINEUR=P.PRO_VERSION_MINEUR AND PRO_VERSION_RELEASE>P.PRO_VERSION_RELEASE
		OR PRO_VERSION_MAJEUR=P.PRO_VERSION_MAJEUR AND PRO_VERSION_MINEUR=P.PRO_VERSION_MINEUR AND PRO_VERSION_RELEASE=P.PRO_VERSION_RELEASE AND PRO_VERSION_BUILD>P.PRO_VERSION_BUILD));

Un détail qui n'en est pas un : une ou plusieurs de tes 4 colonnes peuvent-elles contenir NULL ? Si oui, il te faudra ajuster la requête.

[tatayo]

Et avec une jointure:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT p.*
FROM TR_PROJET P
LEFT OUTER JOIN TR_PROJET P2 ON P2.PRO_DESIGNATION=P.PRO_DESIGNATION
	AND (P2.PRO_VERSION_MAJEUR>P.PRO_VERSION_MAJEUR 
		OR P2.PRO_VERSION_MAJEUR=P.PRO_VERSION_MAJEUR AND PRO_VERSION_MINEUR>P.PRO_VERSION_MINEUR
		OR P2.PRO_VERSION_MAJEUR=P.PRO_VERSION_MAJEUR AND P2.PRO_VERSION_MINEUR=P.PRO_VERSION_MINEUR AND P2.PRO_VERSION_RELEASE>P.PRO_VERSION_RELEASE
		OR P2.PRO_VERSION_MAJEUR=P.PRO_VERSION_MAJEUR AND P2.PRO_VERSION_MINEUR=P.PRO_VERSION_MINEUR AND P2.PRO_VERSION_RELEASE=P.PRO_VERSION_RELEASE AND P2.PRO_VERSION_BUILD>P.PRO_VERSION_BUILD)
WHERE P2.id_k IS NULL

Du coup, je me demande si la version "concaténation" n'est pas plus lisible ?

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT p.*
FROM TR_PROJET P
LEFT OUTER JOIN TR_PROJET P2 ON P2.PRO_DESIGNATION=P.PRO_DESIGNATION
	AND P2.PRO_VERSION_MAJEUR || "." || P2.PRO_VERSION_MINEUR || "." ||  P2.PRO_VERSION_RELEASE || "." || P2.PRO_VERSION_BUILD > P.PRO_VERSION_MAJEUR || "." || P.PRO_VERSION_MINEUR || "." ||  P.PRO_VERSION_RELEASE || "." || P.PRO_VERSION_BUILD
WHERE P2.id_k IS NULL

Tatayo.

[al1_24]

Du coup, je me demande si la version "concaténation" n'est pas plus lisible ?

Mais elle n'est plus valide si le numéro de version dépasse un chiffre parce qu'on passe par un tri alphabétique et non plus numérique.

[skuatamad]

Moi je me contenterais de "simuler" l'approche row_number (qui est la bonne méthode) de la sorte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
mysql> create table TR_PROJET (PRO_ID_K int, PRO_DESIGNATION varchar(10), PRO_VERSION_MAJEUR int, PRO_VERSION_MINEUR int, PRO_VERSION_RELEASE int, PRO_VERSION_BUILD int);
Query OK, 0 rows affected (0.11 sec)
 
mysql> insert into tr_projet values (1 , 'Parking' , 1 , 1 , 14 , 12),
    -> ( 2 , 'Parking' , 1 , 2 , 19 , 33 ),
    -> ( 3 , 'Parking' , 2 , 2 , 18 , 27 ),
    -> ( 4 , 'Parking' , 1 , 3 , 11 , 78 ),
    -> ( 5 , 'Parking' , 2 , 1 , 19 , 23 ),
    -> ( 6 , 'Cinéma' , 1 , 1 , 23 , 38 ),
    -> ( 7 , 'Cinéma' , 1 , 2 , 34 , 98);
Query OK, 7 rows affected (0.19 sec)
Records: 7  Duplicates: 0  Warnings: 0
 
mysql> select * from TR_PROJET;
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
| PRO_ID_K | PRO_DESIGNATION | PRO_VERSION_MAJEUR | PRO_VERSION_MINEUR | PRO_VERSION_RELEASE | PRO_VERSION_BUILD |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
|        1 | Parking         |                  1 |                  1 |                  14 |                12 |
|        2 | Parking         |                  1 |                  2 |                  19 |                33 |
|        3 | Parking         |                  2 |                  2 |                  18 |                27 |
|        4 | Parking         |                  1 |                  3 |                  11 |                78 |
|        5 | Parking         |                  2 |                  1 |                  19 |                23 |
|        6 | Cinéma          |                  1 |                  1 |                  23 |                38 |
|        7 | Cinéma          |                  1 |                  2 |                  34 |                98 |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
7 rows in set (0.00 sec)
 
mysql> select t.*
    ->   from TR_PROJET t
    ->  where t.PRO_ID_K = (select t2.PRO_ID_K
    ->                        from TR_PROJET t2
    ->                       where t2.PRO_DESIGNATION = t.PRO_DESIGNATION
    ->                       order by PRO_VERSION_MAJEUR desc, PRO_VERSION_MINEUR desc, PRO_VERSION_RELEASE desc, PRO_VERSION_BUILD desc
    ->                       limit 1);
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
| PRO_ID_K | PRO_DESIGNATION | PRO_VERSION_MAJEUR | PRO_VERSION_MINEUR | PRO_VERSION_RELEASE | PRO_VERSION_BUILD |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
|        3 | Parking         |                  2 |                  2 |                  18 |                27 |
|        7 | Cinéma          |                  1 |                  2 |                  34 |                98 |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
2 rows in set (0.00 sec)
 
mysql>

[tatayo]

Mais elle n'est plus valide si le numéro de version dépasse un chiffre parce qu'on passe par un tri alphabétique et non plus numérique.

Cépafo
Du coup je me demande si on ne peut pas faire autrement:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT p.*
FROM TR_PROJET P
LEFT OUTER JOIN TR_PROJET P2 ON P2.PRO_DESIGNATION=P.PRO_DESIGNATION
	AND P2.PRO_VERSION_MAJEUR * 1000000 + P2.PRO_VERSION_MINEUR * 10000 +  P2.PRO_VERSION_RELEASE * 100+ P2.PRO_VERSION_BUILD > P.PRO_VERSION_MAJEUR * 1000000 + P.PRO_VERSION_MINEUR * 10000 +  P.PRO_VERSION_RELEASE * 100+ P.PRO_VERSION_BUILD
WHERE P2.id_k IS NULL

Ici la version 1 | 1 | 14 | 12 devient 1011412.
On peut adapter les coefficients, ici je considère que les numéros ont au plus 2 chiffres. Avec 4 chiffres, il suffit de doubler le nombre de 0.


Tatayo.

[J1]

Moi je me contenterais de "simuler" l'approche row_number (qui est la bonne méthode) de la sorte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
mysql> create table TR_PROJET (PRO_ID_K int, PRO_DESIGNATION varchar(10), PRO_VERSION_MAJEUR int, PRO_VERSION_MINEUR int, PRO_VERSION_RELEASE int, PRO_VERSION_BUILD int);
Query OK, 0 rows affected (0.11 sec)
 
mysql> insert into tr_projet values (1 , 'Parking' , 1 , 1 , 14 , 12),
    -> ( 2 , 'Parking' , 1 , 2 , 19 , 33 ),
    -> ( 3 , 'Parking' , 2 , 2 , 18 , 27 ),
    -> ( 4 , 'Parking' , 1 , 3 , 11 , 78 ),
    -> ( 5 , 'Parking' , 2 , 1 , 19 , 23 ),
    -> ( 6 , 'Cinéma' , 1 , 1 , 23 , 38 ),
    -> ( 7 , 'Cinéma' , 1 , 2 , 34 , 98);
Query OK, 7 rows affected (0.19 sec)
Records: 7  Duplicates: 0  Warnings: 0
 
mysql> select * from TR_PROJET;
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
| PRO_ID_K | PRO_DESIGNATION | PRO_VERSION_MAJEUR | PRO_VERSION_MINEUR | PRO_VERSION_RELEASE | PRO_VERSION_BUILD |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
|        1 | Parking         |                  1 |                  1 |                  14 |                12 |
|        2 | Parking         |                  1 |                  2 |                  19 |                33 |
|        3 | Parking         |                  2 |                  2 |                  18 |                27 |
|        4 | Parking         |                  1 |                  3 |                  11 |                78 |
|        5 | Parking         |                  2 |                  1 |                  19 |                23 |
|        6 | Cinéma          |                  1 |                  1 |                  23 |                38 |
|        7 | Cinéma          |                  1 |                  2 |                  34 |                98 |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
7 rows in set (0.00 sec)
 
mysql> select t.*
    ->   from TR_PROJET t
    ->  where t.PRO_ID_K = (select t2.PRO_ID_K
    ->                        from TR_PROJET t2
    ->                       where t2.PRO_DESIGNATION = t.PRO_DESIGNATION
    ->                       order by PRO_VERSION_MAJEUR desc, PRO_VERSION_MINEUR desc, PRO_VERSION_RELEASE desc, PRO_VERSION_BUILD desc
    ->                       limit 1);
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
| PRO_ID_K | PRO_DESIGNATION | PRO_VERSION_MAJEUR | PRO_VERSION_MINEUR | PRO_VERSION_RELEASE | PRO_VERSION_BUILD |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
|        3 | Parking         |                  2 |                  2 |                  18 |                27 |
|        7 | Cinéma          |                  1 |                  2 |                  34 |                98 |
+----------+-----------------+--------------------+--------------------+---------------------+-------------------+
2 rows in set (0.00 sec)
 
mysql>

Voilà qui met tout le monde d'accord. Efficace et plus simple encore que la méthode que je proposais.
Bien vu.