Requêtes: Retrouver un champ associé à un champ regroupé	Author(s)

--- Posted by Michel Walsh ---

Retrouver un champ associé à un champ regroupé

Dans une requête totale (group by), on peut aisément obtenir la valeur "agglomérée", mais comment peut-on retrouver les autres valeurs qui lui sont associées, dans le même enregistrement?

Si on possède les trois champs: BookID, DateDeSortie, EmprumpteurID il est aisé d'obtenir la dernière date de sortie pour chaque bouquin, mais de répondre également à la question: "... et par qui il fut emprunté?" ce n'est pas nécessairement trivial. On ne peut pas faire: 

SELECT BookID, Max(DateDeSortie), Last(EmprumpteurID) ... GROUP BY BookID

car rien ne nous assure que Last(EmprumpteurID) est soutiré du même enregistrement que Max(DateDeSortie), un peu comme si on avait utilisé Min(EmprumpteurID) au lieu de Last(EmprumpteurID). Notons aussi que Last réfère au dernier enregistrement vu par l'engin, par la procédure de solution, lors de l'exécution de la requête. On devrait donc l'appeler "un échantillon quelconque de ce groupe" plutôt que Last, ou First.

Revenant à notre problème. Notons que ce problème est relativement général. Considérer ainsi, pour illustration, que c'est le même problème que d'obtenir "le nombre de pages du dernier bouquin publié par chaque auteur", ou "quel est le fournisseur du meilleur prix pour chaque item", etc.

Il existe plusieurs solution pour ce problème. La plus facile est probablement d'utiliser une cascade de requêtes.

1. Cascade de requêtes
   1. Faire une nouvelle requête.
   2. Amener la table.
   3. Appuyer sur le bouton de sommation pour faire apparaître la ligne Total.
   4. Amener BookId dans la grille, conserver le GroupBy.
   5. Amener DateDeSortie, changer le GroupBy en Max.
   6. Sauvegarder, Q1.
   7. Faire une nouvelle requête, amener la table et la requête Q1.
   8. Joindre, si ce n'est déjà fait, les champs BookID entre eux.
   9. Joindre le champ DateDeSortie avec Q1.MaxDateDeSortie.
   10. Amener le champ BookID et EmptumpteurID. 

   La solution est livrée.

   Quelqu'un peut préférer une seule requête au lieu de deux. C'est possible. Des ces solutions possibles, dégageons une première solution qui implique une sous requête.

2. Sub-Select Query

   1. SELECT Q.BookID, Q.EmprumpteurID FROM TableName As Q

   est trivial, on a simplement utilisé un alias pour la table. Maintenant, il nous faut ajouter un critère du genre: OÙ la date de sortie, de ce livre, = Max(dateDeSortie)

   2. WHERE Q.DateDeSortie = (SELECT Max(T.DateDeSortie)
                   FROM TableName As T
                   WHERE T.BookID = Q.BookID)

   où on reconnaîtra l'utilisation d'un autre exemplaire de la table, avec alias T, duquel exemplaire on extirpe le maximum de date de sortie. Noter la formulation "pour le même livre" qui relie indirectement les deux exemplaires utilisés de la table. Il faut aussi noter qu'utiliser un alias n'entraîne pas une "copie" physique de la table, un alias est seulement une référence, et aucune donnée n'est physiquement copiée.

3. Total 

   On peut utiliser une seule requête "totale" (GroupBy), mais sa formulation n'est pas aussi simple que la solution naïvement proposée initialement.

    

   1. SELECT Q.BookID, First(Q.EmprumpteurID)
          FROM TableName As Q INNER JOIN TableName As T
                          ON Q.BookID=T.BookID
          GROUP BY Q.BookID, Q.DateDeSortie
          HAVING Q.DateDeSortie = Max(T.DateDeSortie)

   Ici encore, on utilise deux exemplaires (au sens de références, pas au sens de copies) de la même table, un des exemplaires est utilisé pour repérer le maximum de la date de sortie, pour un bouquin donné. Les règles de syntaxe SQL requièrent l'utilisation d'un clause HAVING, au lieu de WHERE, puisqu'on utilise Max dans le critère. Remarquons l'utilisation d'un INNER JOIN pour spécifier l'interrelation (même livre) pour les deux exemplaires de la table.

    Note: Il faut utiliser un HAVING car un WHERE est évalué AVANT un agrégat. Ayant un agrégat dans une clause WHERE est donc un non-sens: WHERE 4=MAX(...), est un non sens puisque MAX n'est pas encore évalué lorsque WHERE est à être évalué. On utilise donc un HAVING, car HAVING est évalué après les agrégations.

    

4. Jet 4.0
   Avec Jet 4.0, on peut s'inspirer de la solution 3, mais portant le INNER JOIN en premier plan, et en laissant le regroupement en second, ou, si on préfère, on peut combiner les deux requêtes de la solution 1 en une seule requête:

   1. SELECT Q.BookID, Q.EmprumpteurID
          FROM TableName As Q INNER JOIN
              (SELECT BookID, Max(DateDeSortie) As S
               FROM TableName
               GROUP BY BookID)  As T
              ON Q.BookId=T.BookId AND Q.DateDeSortie= T.S