SGBD & SQL - Apprendre des méthodes permettant d'identifier la ligne la plus récente ou la plus ancienne pour un critère,

Un billet blog proposé par escartefigue

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 :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 :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()
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
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
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
*MySQL a intégré les fonctions OLAP dans la V8