--- 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 "aglomé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é de savoir la dernière date de sortie pour chaque bouquin, mais de répondre également à la question: "... et par qui il fut emprumpté?" 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 soustiré du même enregistrement que Max(DateDeSortie), un peu comme si on avait utilisé Min(EmprumpteurID) au lieu de Last(Emprumpteur). 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 un cascase 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îre 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être, 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. Du groupe de solutions, une première implique une sous-requête.

2. Sub-Select Query

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

   est trivial, on a simplement utiliser 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 use afin de repérer 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.

3. Total 

   On peut utiliser une seule requêtre "totale" (GroupBy), mais sa formulation n'est pas aussi simple que la solution naïevement 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)

   2. Ici encore, on utilise deux exemplaires 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'inter-relation (même livre) pour les deux exemplaires de la table.

    

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