Top 11 à 15 avec regroupement

Publié le par

Suite au billet précédent, on souhaite obtenir le TOP 11 à 15 des Id (par ordre croissant) liés à chaque personne.

Création de la table et insertion des données
La structure de la table est un peu différente du billet précédent pour visualiser facilement la sélection de la requête :

Public Function CreationTableTop2()
   Const cInsert As String = "INSERT INTO tTop2 (Id, Nom) VALUES "
   Dim i As Long, j As Long
   Dim aNoms As Variant
 
   With DoCmd
      .SetWarnings False
 
      .RunSQL "CREATE TABLE tTop2 (Id LONG, Nom CHAR, " & _
              "CONSTRAINT PrimaryKey PRIMARY KEY(Id, Nom));"
 
      aNoms = Array("Albert", "Marcel", "Bernard", "Dominique")
 
      For i = LBound(aNoms) To UBound(aNoms)
         For j = 1 To 25
            .RunSQL cInsert & "(" & j & ",'" & aNoms(i) & "');"
         Next j
      Next i
 
      .SetWarnings True
   End With
   MsgBox "Création terminée"
End Function

 
Résultat attendu

Id Nom
11 Albert
12 Albert
13 Albert
14 Albert
15 Albert
11 Bernard
12 Bernard
…

 
La requête SQL
Une solution possible est :


SELECT * 
FROM   ttop2 AS T1 
WHERE  id > ALL (SELECT TOP 10 id 
                 FROM   ttop2 T 
                 WHERE  T.nom = T1.nom 
                 ORDER  BY id) 
       AND id <= ANY (SELECT TOP 15 id 
                      FROM   ttop2 T 
                      WHERE  T.nom = T1.nom 
                      ORDER  BY id) 
ORDER  BY nom, 
          id

 
Explications
La clause Where va filtrer les lignes sur deux critères complémentaires :
Toutes les lignes retournées doivent avoir un Id > à tous les Id du TOP 10 du même NOM ET un Id <= à au moins un Id du TOP 15 du même NOM.
Finalement, seules les Id du TOP 11 à 15 répondent aux deux critères et les lignes correspondantes sont retournées.

Autre solution
Il est possible de substituer >ALL et <=Any par Not In et In :

SELECT *
    FROM tTop2 AS T1
    WHERE id NOT IN  (
    SELECT TOP 10 Id 
        FROM tTop2 T 
        WHERE T.Nom=T1.Nom 
        ORDER BY Id) 
        AND id IN (
    SELECT TOP 15 Id 
        FROM tTop2 T 
        WHERE T.Nom=T1.Nom 
        ORDER BY Id)
    ORDER BY Nom, id;
 

Performance
Il est recommandé d’indexer sans doublons la colonne ‘Nom’ sur laquelle porte la clause Where.
Compte-tenu de la présence de 2 sous-requêtes, cette requête est adaptée pour une table possédant moins de 1000 lignes…

Si vous avez d’autres solutions, n’hésitez pas à les placer en commentaire.

@+

Philippe

Laisser un commentaire