Discussion :

[informer]

Bonjour à tous,

Le comportement des requêtes agrégatives sont toujours surprenantes. Pour preuve si j’ai les valeurs suivantes par exemple:

Date	seq	Id	Val
01.12.2018	6	Aa	Qmsd
01.12.2018	1	Aa	Try
01.11.2017	11	Aa	Vieira
01.06.2000	4	Zz	Rfcd
31.01.2000	8	Zz	Okrfg

Et je veux pour chaque id, la val de l’enregistrement qui a le tuple (date, seq) le plus grande.

Date	seq	Id	Val
01.12.2018	6	Aa	Qmsd
01.06.2000	4	Zz	Rfcd

Première constatation si je contruis une requête qui

• regroupe sur Id
• applique Max sur date et sur seq

Date	seq	Id
01.12.2018	11	Aa
01.06.2000	8	Zz

Ce qui est effectivement normal

Question

Comment sans faire une sous requête obtenir ceci

Date	seq	Id	Val
01.12.2018	6	Aa	Qmsd
01.06.2000	4	Zz	Rfcd

[hyperion13]

Salut,
Il suffirait d'utiliser une fct de domaine DLookup() ou DMAx() en concatenant Date Seq id et Val et d'ajouter 3 critères date<=date id=id et Seq<=Seq. Bien entendu il faudra encadrer les critères avec des quote double-quote et dièse.

[tee_grandbois]

bonjour,
Une solution est d'agréger les 2 valeurs (ou plus) du regroupement pour obtenir un résultat cohérent: Max(Format([DateId],"0000000") & Format([seq],"00000"))
Il faut prendre soin de formater correctement les 2 valeurs pour ensuite les extraire:
Date: CVDate(Left(Max(Format([DateId],"0000000") & Format([seq],"00000")),7))
Seq : Val(Right(Max(Format([DateId],"0000000") & Format([seq],"00000")),5))

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT CVDate(Left(Max(Format([DateId],"0000000") & Format([seq],"00000")),7)) AS W_DateID
, Val(Right(Max(Format([DateId],"0000000") & Format([seq],"00000")),5)) AS W_Seq, T_Table5.Id, First(T_Table5.Val) AS Val
FROM T_Table5
GROUP BY T_Table5.Id;

[informer]

Merci à tous les deux.

tee_grandbois, si je comprends bien ton excellente solution

On sélection :

• Côté date la partie date du Max de la concaténation du tuple (date,seq) donc left de 7
• Côté Seq la partie seq du Max de la concaténation du tuple (date,seq) donc right de 5 car format sur 5 positions car tu considères pour l’exemple que seq ne peut dépasser 99999

Pour mémoire format (valeur, "00000") remplace autant de zéro qu’il y a de digits dans le nombre donc ramène bien 00001 si valeur est 1 et 00020 si valeur est 20

Ma requête sur la base de celle de tee_grandbois
Pour le calcul du Max :

• Utilise format(date,"yyyymmdd") donc left de 8
• Les champs du calcul du max sont des "Expression"
  

Pour les champs à afficher non pivots du group by:

• Tous les champs sont en first

Ce qui me donne comme SQL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Left(Max(Format([dDate],"yyyymmdd") & Format([Seq],"00000")),8) AS MaxDate, Right(Max(Format([dDate],"yyyymmdd") & Format([Seq],"00000")),5) AS MaxSeq, First(tblMax.dDate) AS PremierDedDate, First(tblMax.Seq) AS PremierDeSeq, tblMax.id, First(tblMax.Val) AS PremierDeval
FROM tblMax
GROUP BY tblMax.id

On obtient
MaxDate MaxSeq PremierDedDate PremierDeiSeq id PremierDeval
20181201 00006 01/12/2018 6 Aa Qmsd
20000601 00004 01/06/2000 4 Zz Rfcd

Optimisation

Un simple Max(dDate) est suffisant et permet de garder le champ au format date
La formule de tee_grandbois est pertinente pour le Max de la séquence associée d’où la requête suivante que l’on peut ensuite caster en numérique.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Max([dDate]) AS MaxDate, Cint(Right(Max(Format([dDate],"yyyymmdd") & Format([Seq],"00000")),5)) AS MaxSeq, First(tblMax.dDate) AS PremierDedDate, First(tblMax.Seq) AS PremierDeSeq, tblMax.id, First(tblMax.Val) AS PremierDeval
FROM tblMax
GROUP BY tblMax.id

Ca marche impec. Merci encore tee_grandbois

[informer]

Salut,
Il suffirait d'utiliser une fct de domaine DLookup() ou DMAx() en concatenant Date Seq id et Val et d'ajouter 3 critères date<=date id=id et Seq<=Seq. Bien entendu il faudra encadrer les critères avec des quote double-quote et dièse.

Hyperion, cela serait sympa si tu me posté le SQL pour que je puisse l’analyser

Merci

[tee_grandbois]

bonjour informer,

Cette solution n’est valable que sur des champs qui peuvent s’exprimer au format numérique. Dans le cas d’un ou plusieurs champs alphanumériques composant le Max, je pense qu’on peut s’attendre à des résultats surprenants

non c'est possible puisqu'un caractère alphabétique a une valeur numérique, on peut donc faire un tri et trouver le plus grand, il faut juste connaitre la taille du champ à concaténer : à supposer que Seq est du texte de 18 caractères on fera la concaténation comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Max(Format([dDate];"aaaammjj") & Chaîne(18-Nz(NbCar([seq]);"");Espace(1)) & [seq])

et pour récupérer la valeur initiale du champ texte, on enlève (ou pas) les espaces ajoutés avec Trim()

la requête complète:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Mid(Max(Format([dDate],"yyyymmdd") & String(18-Nz(Len([seq]),""),Space(1)) & [seq]),1,8) AS MaxDate, Trim(Mid(Max(Format([dDate],"yyyymmdd") & String(18-Nz(Len([seq]),""),Space(1)) & [seq]),9,18)) AS MaxSeq, First(tblMax.dDate) AS PremierDedDate, First(tblMax.Id) AS PremierDeId, First(tblMax.Seq) AS PremierDeSeq, First(tblMax.Val) AS PremierDeVal
FROM tblMax
GROUP BY tblMax.Id;

[informer]

Tee_grandbois,

Tu as tout à fait raison, je préviens seulement que le résultat pour de l’alphanumérique c’est plus touchy car il y a les caractères ponctués et les majuscules.

Encore merci pour ton aide particulièrement précieuse.