Voir le flux RSS

escartefigue

Selectionner la ligne la plus récente pour un critère

Noter ce billet
par , 14/06/2017 à 15h22 (113 Affichages)
Le sujet étant archi récurrent, voici plusieurs méthodes permettant d'identifier la ligne la plus récente ou la plus ancienne pour un critère.

Tout d'abord, il convient de rappeler que les identifiants techniques attribués par le SGBD (identity column, auto_incrément...) ne doivent en aucun cas être utilisés pour ce besoin. En effet, si ces identifiants sont souvent chronologiques en tout début de vie d'une table, quand il y a eu peu d'insertions, ce n'est rapidement plus le cas, dès que cette table vit un peu. Ne tombez donc pas dans ce piège !

Donc, dans les exemples ci-dessous, on utilisera une colonne de type timestamp.

Soit les tables suivantes :
TBB7 :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
B7IDEN   B7CPTE     B7JOUR   B7DBCR 
------ ---------- ---------- ------ 
     1 1234567890 2017-01-02      0 
     2 4455667788 2017-01-15      0 
     3 0012005564 2017-06-14      1 
     4 7564534231 2017-01-14      1 
     5 1111122222 2017-01-16      1
La PK est B7IDEN

TBB8 :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
B8IDEN B8SEQN      B8MONT                 B8DTHR          
------ ------ ----------------- --------------------------
     1      1           145.280 2017-06-14-14.41.06.079306
     1      2           311.470 2017-06-14-14.41.06.097249
     2      2           -16.800 2017-06-14-14.41.06.098156
     2      1            33.210 2017-06-14-14.41.06.098746
     2      3            -5.100 2017-06-14-14.41.06.099171
     4      2           155.250 2017-06-14-14.41.06.103170
     4      3           800.400 2017-06-14-14.41.06.103651
La PK est B8IDEN+B8SEQN
avec une contrainte sur B8IDEN qui fait référence à B7IDEN

L'éternelle question est : comment rechercher la ligne détail la plus récente pour chaque ligne entête

Méthode 1 : utilisation de MAX()
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
  SELECT B7IDEN                       
       , B7CPTE                       
       , B8SEQN                       
       , B8MONT                       
       , B8DTHR                       
  FROM TBB7                           
  INNER JOIN TBB8  B8                 
    ON  B8IDEN = B7IDEN               
  WHERE B8DTHR =                      
       (SELECT MAX(B8DTHR)            
        FROM TBB8 S8                  
        WHERE S8.B8IDEN = B8.B8IDEN)  
  ;
Méthode 2 : utilisation de EXISTS
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 B7IDEN                        
        , B7CPTE                        
        , B8SEQN                        
        , B8MONT                        
        , B8DTHR                        
   FROM TBB7                            
   INNER JOIN TBB8  B8                  
     ON  B8IDEN = B7IDEN                
   WHERE NOT EXISTS                     
        (SELECT 1                       
         FROM TBB8 S8                   
         WHERE S8.B8IDEN = B8.B8IDEN    
           AND S8.B8DTHR > B8.B8DTHR)   
   ;
Méthode 3 : utilisation de RANK ou DENSE_RANK
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
   WITH CTE1 (RG, C2, C3, C4, C5, C6) AS      
       ( SELECT DENSE_RANK()                  
                   OVER (PARTITION BY B7IDEN  
                         ORDER BY B8DTHR DESC)
              , B7IDEN                        
              , B7CPTE                        
              , B8SEQN                        
              , B8MONT                        
              , B8DTHR                        
         FROM TBB7                            
         INNER JOIN TBB8                      
           ON  B8IDEN = B7IDEN )              
   SELECT C2, C3, C4, C5, C6                  
   FROM CTE1                                  
   WHERE RG=1                                 
   ;
Cette dernière méthode ne peut pas être utilisée avec MySQL ou Access qui n'intègrent pas les fonctions OLAP

Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog Viadeo Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog Twitter Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog Google Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog Facebook Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog Digg Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog Delicious Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog MySpace Envoyer le billet « Selectionner la ligne la plus récente pour un critère » dans le blog Yahoo

Catégories
Sans catégorie

Commentaires

  1. Avatar de tatayo
    • |
    • permalink
    Bonjour,
    J'ajouterai comme méthode l'utilisation d'une jointure externe:
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    SELECT B7IDEN                        
            , B7CPTE                        
            , B8SEQN                        
            , B8MONT                        
            , B8DTHR                        
       FROM TBB7                            
       INNER JOIN TBB8  B8                  
         ON  B8.B8IDEN = B7IDEN
    left outer join TBB8 S8                   
             WHERE S8.B8IDEN = B8.B8IDEN    
               AND S8.B8DTHR > B8.B8DTHR
    where S8.B8IDEN is null

    Tatayo.
  2. Avatar de escartefigue
    • |
    • permalink
    Merci pour ce complément,

    La liste n'est bien sur pas exhaustive, il existe encore d'autres solutions (avec except par exemple)
    En fonction du contexte, la solution la plus optimale peut varier, il faut proto-typer en conditions proches de la production pour choisir celle qui est la plus efficiente.
  3. Avatar de Waldar
    • |
    • permalink
    Hello, bon résumé.

    J'ajouterai les solutions propriétaires à Oracle, PostgreSQL & SQL-Server qui sont elles sont moins portables, offrent des performances de premier ordre.

    Oracle
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
      select b7.b7iden
           , b7.b7cpte
           , max(b8.b8seqn) keep (dense_rank first order by b8.b8dthr desc) as b8seqn
           , max(b8.b8mont) keep (dense_rank first order by b8.b8dthr desc) as b8mont
           , max(b8.b8dthr)                                                 as b8dthr
        from tbb7 b7
        join tbb8 b8 on b8.b8iden = b7.b7iden
    group by b7.b7iden
           , b7.b7cpte;

    PostgreSQL
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
      select distinct on (b7.b7iden)
             b7.b7iden
           , b7.b7cpte
           , b8.b8seqn
           , b8.b8mont
           , b8.b8dthr
        from tbb7 b7
        join tbb8 b8 on b8.b8iden = b7.b7iden
    order by b7.b7iden  asc
           , b8.b8dthr desc;

    SQL-Server
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
         select b7.b7iden
              , b7.b7cpte
              , b8.b8seqn
              , b8.b8mont
              , b8.b8dthr
           from tbb7 b7
    cross apply ( select top 1 tbb8.b8seqn, tbb8.b8mont, tbb8.b8dthr 
                    from tbb8 
                   where tbb8.b8iden = b7.b7iden
                order by tbb8.b8dthr desc) b8;

    À noter qu'à partir d'Oracle 12c, on peut également utiliser cross apply :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
         select b7.b7iden
              , b7.b7cpte
              , b8.b8seqn
              , b8.b8mont
              , b8.b8dthr
           from tbb7 b7
    cross apply ( select tbb8.b8seqn, tbb8.b8mont, tbb8.b8dthr 
                    from tbb8 
                   where tbb8.b8iden = b7.b7iden
                order by tbb8.b8dthr desc
                fetch first 1 rows only) b8;
  4. Avatar de escartefigue
    • |
    • permalink
    Merci également