Discussion :

[voxov]

Bonjour,

Je suis devant un cas apparemment simple qui défie les possibilités du sql.

Les données : table "Referentiel"

SiteRisque 	SiteComptable	Taux
AAA		 123		        0,33
ABC		 123		        0,22
DEF		 123		        0,11
GHV 		 123		        0,33
IJB		 678		        0,80
QSD		 123		        0,33
UJK 		 678		        0,45
UNF		 890		        0,99
YGV	 	 890		        0,25

L'objectif :
Sortir la liste des SiteRisque qui ont le taux maximal, par SiteComptable. S'il y a plusieurs SiteRisque qui ont le même taux maximal par SiteComptable, prendre le SiteRisque minimum parmi ces SiteRisque.

Sur les données ci-dessus, le résultat est le suivant :

SiteComptable	SiteRisque
123		AAA
678		IJB
890		UNF

En Access, j'obtiens le résultat avec 2 requêtes. La seconde utilise la première :

1. Requete "qMaxTaux1"

Code :

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

1
2
3
SELECT Referentiel.SiteComptable, Max(Referentiel.Taux) AS MaxDeTaux
FROM Referentiel
GROUP BY Referentiel.SiteComptable;

2.Requete "qMaxTaux2"

Code :

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

1
2
3
SELECT Min(Referentiel.SiteRisque) AS MinDeSiteRisque, Referentiel.SiteComptable
FROM qMaxTaux1 INNER JOIN Referentiel ON (qMaxTaux1.SiteComptable = Referentiel.SiteComptable) AND (qMaxTaux1.MaxDeTaux = Referentiel.Taux)
GROUP BY Referentiel.SiteComptable;

Rem. Je n'utilise pas de table intermédiaire entre les deux requêtes.


La question : Je voudrais obtenir le résultat avec une seule requête sql (qui peut elle-même contenir une sous-requête, mais pas deux requêtes séparées ni une table intermédiaire).


Merci d'avance pour vos réponses/réactions.

V.

[tatayo]

Bonjour,
Est-ce que ça renvoie le bon résultat ?

Code sql :

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

1
2
3
4
5
6
 
select min(t1.SiteRisque),t1.SiteComptable,t1.taux
from referentiel t1
left outer join referentiel t2. on t1.sitecomptable = t2.sitecomptable and t1.taux < t2.taux
where t2.taux is null
group by t1.SiteComptable,t1.taux

Tatayo.

[Waldar]

Je suis devant un cas apparemment simple qui défie les possibilités du sql.

Sur Oracle Database ça s'écrit en une seule requête :

Code :

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

1
2
3
4
5
  select SiteComptable
       , min(SiteRisque) keep(dense_rank first order by Taux desc) as SiteRisque
    from Referentiel
group by SiteComptable
order by SiteComptable asc;

La question : Je voudrais obtenir le résultat avec une seule requête sql (qui peut elle-même contenir une sous-requête, mais pas deux requêtes séparées ni une table intermédiaire).

Ne pouvez-vous pas tout simplement imbriquer les deux requêtes que vous avez écrites ?

Code :

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

1
2
3
4
5
6
7
8
9
  SELECT min(rfr.SiteRisque) AS MinDeSiteRisque
       , rfr.SiteComptable
    FROM ( SELECT SiteComptable, Max(Taux) AS MaxDeTaux
             FROM Referentiel
         GROUP BY SiteComptable ) as tmx 
    JOIN Referentiel              as rfr
      ON tmx.SiteComptable = rfr.SiteComptable
     AND tmx.MaxDeTaux     = rfr.Taux
GROUP BY rfr.SiteComptable;

[voxov]

Bonjour,
Est-ce que ça renvoie le bon résultat ?

Code sql :

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

1
2
3
4
5
6
 
select min(t1.SiteRisque),t1.SiteComptable,t1.taux
from referentiel t1
left outer join referentiel t2. on t1.sitecomptable = t2.sitecomptable and t1.taux < t2.taux
where t2.taux is null
group by t1.SiteComptable,t1.taux

Tatayo.

Oui, Le résultat est celui attendu.
Merci Tatayo.

[voxov]

Sur Oracle Database ça s'écrit en une seule requête

Merci pour la réponse, mais je n'utilise pas Oracle et je préfère une solution non propriétaire.

[voxov]

Ne pouvez-vous pas tout simplement imbriquer les deux requêtes que vous avez écrites ?

Code :

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

1
2
3
4
5
6
7
8
9
  SELECT min(rfr.SiteRisque) AS MinDeSiteRisque
       , rfr.SiteComptable
    FROM ( SELECT SiteComptable, Max(Taux) AS MaxDeTaux
             FROM Referentiel
         GROUP BY SiteComptable ) as tmx 
    JOIN Referentiel              as rfr
      ON tmx.SiteComptable = rfr.SiteComptable
     AND tmx.MaxDeTaux     = rfr.Taux
GROUP BY rfr.SiteComptable;

La piste est intéressante. J'ai essayé en Access et j'ai un message "Erreur de syntaxe dans la clause FROM". Peut-être que le JOIN (SELECT ... ) dans un FROM n'est pas supporté par Access.
Une alternative ?

[fsmrel]

Bonjour

Je suis devant un cas apparemment simple qui défie les possibilités du sql.

C’est un cas qui ne pose aucune difficulté en SQL, sauf manifestement dans sa variante bridée ACCESS : vos deux requêtes "qMaxTaux1" et "qMaxTaux2" ont été tout simplement réunies en une seule par Waldar et cette dernière requête est valide avec tous les SGBD (à part un apparemment...) : si elle n’est pas exploitable avec ACCESS c’est que celui-ci est retard de quelques trains, en ayant oublié que dans un FROM on peut fournir plus qu’une simple liste de noms de tables (une requêtes ACCESS y comptant syntaxiquement pour une table), mais une liste d’expressions de tables de la forme (SELECT ... FROM...etc.) AS t.
Je viens de m’en assurer avec ACCESS 2003 qui refuse le SELECT de Waldar, et il faudrait s’assurer de ce qu’il en est avec les versions suivantes d’ACCESS (si j’interprète correctement que je lis ici, ça n’a pas bougé).

ACCESS se comporte comme si en algèbre on ne pouvait pas emboîter des expressions dans une expression, au mépris de la propriété de fermeture... C’est ce qui avait fait réagir Hugh Darwen il y a déjà 25 ans de cela.

[fsmrel]

Peut-être quelque chose comme ça conviendrait aussi :

Code SQL :

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

1
2
3
4
5
6
7
SELECT MIN(SiteRisque) AS MinDeSiteRisque, SiteComptable
FROM REFERENTIEL AS x
WHERE (SELECT Count (*)
       FROM  REFERENTIEL AS y
       WHERE x.SiteComptable = y.SiteComptable
       AND x.Taux < y.Taux) < 1
GROUP BY SiteComptable;

[voxov]

Peut-être quelque chose comme ça conviendrait aussi :

Code SQL :

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

1
2
3
4
5
6
7
SELECT MIN(SiteRisque) AS MinDeSiteRisque, SiteComptable
FROM REFERENTIEL AS x
WHERE (SELECT Count (*)
       FROM  REFERENTIEL AS y
       WHERE x.SiteComptable = y.SiteComptable
       AND x.Taux < y.Taux) < 1
GROUP BY SiteComptable;

Oui, cette requête donne le bon résultat en Access 2003.
Merci.

[islamov2000]

Bonjour,

Je suis devant un cas apparemment simple qui défie les possibilités du sql.

Les données : table "Referentiel"

SiteRisque SiteComptable Taux AAA 123 0,33 ABC 123 0,22 DEF 123 0,11 GHV 123 0,33 IJB 678 0,80 QSD 123 0,33 UJK 678 0,45 UNF 890 0,99 YGV 890 0,25

L'objectif :
Sortir la liste des SiteRisque qui ont le taux maximal, par SiteComptable. S'il y a plusieurs SiteRisque qui ont le même taux maximal par SiteComptable, prendre le SiteRisque minimum parmi ces SiteRisque.

Sur les données ci-dessus, le résultat est le suivant :

SiteComptable SiteRisque 123 AAA 678 IJB 890 UNF
En Access, j'obtiens le résultat avec 2 requêtes.

......................

V.

Voici la requete, ca va t'aider

Code :

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

1
2
3
4
5
6
7
8
 
SELECT Min(A.SiteRisque) AS MinDeSiteRisque, B.SiteComptable
FROM Latable AS A INNER JOIN
(SELECT Max(Latable.Taux) AS MaxDeTaux, Latable.SiteComptable
FROM Latable
GROUP BY Latable.SiteComptable)  AS B 
ON (A.SiteComptable = B.SiteComptable) AND (A.Taux = B.MaxDeTaux)
Group by B.SiteComptable,B.MaxDeTaux

Code :

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

1
2
3
4
MinDeSiteRisque    SiteComptable
AAA                123
IJB                678
UNF                890

[fsmrel]

islamov2000,

Hélas ! Votre requête ne peut pas servir à voxov qui a précisé qu’il utilisait ACCESS, lequel n’accepte pas de (SELECT... FROM...) dans un FROM, comme cela fut déjà signalé dans la discussion.

[Chtulus]

Bonsoir,

Me suis emballé j'ai mi +1 à @islamov2000, c'était pour @fsmrel au final....

....

[voxov]

Voici la requete, ca va t'aider

Code :

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

1
2
3
4
5
6
7
8
 
SELECT Min(A.SiteRisque) AS MinDeSiteRisque, B.SiteComptable
FROM Latable AS A INNER JOIN
(SELECT Max(Latable.Taux) AS MaxDeTaux, Latable.SiteComptable
FROM Latable
GROUP BY Latable.SiteComptable)  AS B 
ON (A.SiteComptable = B.SiteComptable) AND (A.Taux = B.MaxDeTaux)
Group by B.SiteComptable,B.MaxDeTaux

Code :

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

MinDeSiteRisque    SiteComptable

Oui, c'est bon en Access aussi.
Contrairement à ce que j'avais observé hier, cela prouve que Access supporte le JOIN (SELECT ...) dans une clause FROM. Simplement, il faut mettre "INNER JOIN", pas seulement "JOIN" sinon message d'erreur.
Merci.

[islamov2000]

pour Expert Confirmé Sénior @fsmrel et Modérateur @ Chtulus

Avant de s'anticiper les commentaires il faut "penser et repenser".
Au moins vous mettez vos temps pour essayer les exemples.
Je m'excuse "Expert Confirmé Sénior" et "Modérateur".

[fsmrel]

Bonsoir,


Comme disait Roland Magdane qui en l’occurrence avait une tendance à une sympathique dyslexie : « je récapépète depuis le bédut ».


A propos de la requête de Waldar :

Code SQL :

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

1
2
3
4
5
6
7
8
9
  SELECT min(rfr.SiteRisque) AS MinDeSiteRisque
       , rfr.SiteComptable
    FROM ( SELECT SiteComptable, Max(Taux) AS MaxDeTaux
             FROM Referentiel
         GROUP BY SiteComptable ) as tmx 
    JOIN Referentiel              as rfr
      ON tmx.SiteComptable = rfr.SiteComptable
     AND tmx.MaxDeTaux     = rfr.Taux
GROUP BY rfr.SiteComptable;

Cette requête est conforme à la norme SQL qui autorise à ne pas coder le mot INNER, auquel cas il est implicite ; je cite à ce propos le document de référence SQL/Foundation : WG3:HBA-003 = H2-2003-305 = 5WD-02-Foundation-2003-09, WD 9075-2 (SQL/Foundation), September, 2003, page 313 :

If a <qualified join> or <natural join> is specified and a <join type> is not specified, then INNER is implicit.

Toutefois, quand on veut exécuter cette requête dans le contexte ACCESS 2003 :

1) Il faut remplacer JOIN par INNER JOIN car ACCESS 2003 n’a pas manifestement pas tenu compte de la norme. Pour les versions suivantes d’ACCESS je ne me prononce pas puisque je n’en dispose pas, mais si quelqu’un peut fournir le lien vers une référence officielle à ce propos, on lui en sera reconnaissant.

2) Remplaçons donc JOIN par INNER JOIN. ACCESS 2003 transforme alors d’autorité la requête sans demander l’avis du développeur, en remplaçant une paire de parenthèses par une paire de crochets et en ajoutant un point-virgule :



Mais à l’exécution de la requête, on a droit au message :



Fort perplexe, j’ai cherché et fini par dénicher un spécialiste particulièrement perspicace, lequel a préconisé d’ajouter un point derrière le crochet fermant :



Ça ressemble étonnamment à un objet d’une classe...

Peu importe, la présence de ce point fait que la requête fonctionne ! Manifestement il y a un bug qui n’avait pas été détecté par Microsoft dans la version d’ACCESS dont je dispose (ou dont l'éventuelle correction ne m'a pas été signalée, bien que je sois un utilisateur enregistré)...

Je viens de m’en assurer avec ACCESS 2003 qui refuse le SELECT de Waldar, et il faudrait s’assurer de ce qu’il en est avec les versions suivantes d’ACCESS (si j’interprète correctement que je lis ici, ça n’a pas bougé).

Dans le cas d’ACCESS 2003 l’énigme a donc une explication il faut en passer par le bricolage évoqué ci-dessus, non documenté par l’éditeur. J’ai cru comprendre que dans les versions suivantes, ce bricolage à coups de crochets, de points-virgules et de points n’a plus lieu d’être. Est-ce à partir d’ACCESS 2007, 2010 ?

Il n’en demeure pas moins que la documentation d’ACCESS n’a manifestement pas été mise à jour depuis un bon moment et ne tient pas compte de la norme qui décrit les « tables dérivées », c'est-à-dire les subqueries sous le contrôle de FROM (cf. pages 301 et 303 du document SQL/Foundation cité plus haut).


Voici ce qui écrit en 2013 dans la documentation ACCESS :




tableexpression ne représente donc qu’une liste de noms de tables, sans faire mention du type hypothétique des tables elles-mêmes (table de base, vue, requête, table dérivée, instantané...), ce qui est une source d’ambiguïtés et de quiproquos.


A contrario, les autres SGBD comme Oracle, DB2, PostgreSQL ou MS SQL Server sont exhaustifs dans leur présentation de la clause FROM. Par exemple, avec MS SQL Server :




Et MS SQL Server fournit les exemples qui vont bien :




J’en conclus qu’il ne faut accorder aucune confiance à la doc officielle d’ACCESS, en tout cas celle qui s’adresse au développeur, trop indigente en la matière.

Peut-être quelque chose comme ça conviendrait aussi :

Code SQL :

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

1
2
3
4
5
6
7
SELECT MIN(SiteRisque) AS MinDeSiteRisque, SiteComptable
FROM REFERENTIEL AS x
WHERE (SELECT Count (*)
       FROM  REFERENTIEL AS y
       WHERE x.SiteComptable = y.SiteComptable
       AND x.Taux < y.Taux) < 1
GROUP BY SiteComptable;

Quelqu’un a attribué un vote négatif à cette requête : il eut mieux fait de la tester, car elle fonctionne parfaitement (voxov confirme), et surtout elle vient en droite ligne de l’ouvrage du maître incontesté de la théorie relationnelle, C.J. Date : Relational Database, Writings 1994-1997, « Installment Number 47, Quota Queries », à la page 163.

Voici la requete, ca va t'aider

Code :

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

1
2
3
4
5
6
7
8
 
SELECT Min(A.SiteRisque) AS MinDeSiteRisque, B.SiteComptable
FROM Latable AS A INNER JOIN
(SELECT Max(Latable.Taux) AS MaxDeTaux, Latable.SiteComptable
FROM Latable
GROUP BY Latable.SiteComptable)  AS B 
ON (A.SiteComptable = B.SiteComptable) AND (A.Taux = B.MaxDeTaux)
Group by B.SiteComptable,B.MaxDeTaux

A condition d'utiliser une version d’ACCESS supérieure à 2003, c'est d'accord, cette requête fonctionne puisque c’est la jumelle de celle de Waldar, avec le mot INNER en plus.

islamov2000,

Hélas ! Votre requête ne peut pas servir à voxov qui a précisé qu’il utilisait ACCESS, lequel n’accepte pas de (SELECT... FROM...) dans un FROM, comme cela fut déjà signalé dans la discussion.

Que les utilisateurs d’une version d’ACCESS plus récente que la mienne me pardonnent cette affirmation qui est, comme je l'ai expliqué, la conséquence d’un bug d’ACCESS 2003 et d’une documentation officielle encore incomplète en 2013...

pour Expert Confirmé Sénior @fsmrel et Modérateur @ Chtulus

Avant de s'anticiper les commentaires il faut "penser et repenser".
Au moins vous mettez vos temps pour essayer les exemples.
Je m'excuse "Expert Confirmé Sénior" et "Modérateur".

Voyez-vous, membre chevronné, vous avez pu être froissé et j’en suis désolé, mais quoi que vous en jugiez, vos interlocuteurs pensent et repensent, testent soigneusement ce qu’ils écrivent (y compris en reprenant les requêtes du maître C.J. Date), avec tous les SGBD qu’ils ont sous la main, et une foule de forumeurs peuvent en témoigner. Mais, concours de circonstances malheureux, nul n’est à l’abri d’un bug de la part de l’éditeur et du laxisme d’une documentation officielle. Avec un peu de recul, j'espère que vous en conviendrez.

Bonne continuation.