Discussion :

[Fred_76]

Bonjour,

Je dois effectuer une requête basée sur 2 fichiers et retourné uniquement 1 seul enregistrement du second fichier.
Pour mieux vous expliquer cela, voici la situation :

Un fichier Stock avec :

id_stock, ref, descr,...

Un fichier historique avec :

id_historique, id_stock, date_der_mouv, valeur

Ma requête doit me rtourner la dernière ligne de l'historique pour chaque pièce (celle dont la date est la plus grande)
Je cale.......

Un exemple si vous ne voyez pas bien :

Table1 :
piece_1 - 12345 - Roulement
piece_2 - 45678 - Ecrou
Piece_3 - 98765 - Vis

Table2 :

piece_1 - 20050901 - 15,50
piece_2 - 20050710 - 2,70
piece_2 - 20050810 - 2,90
piece_2 - 20050803 - 2,75
piece_2 - 20050808 - 2,80
Piece_3 - 20050815 - 5,10
piece_1 - 20050815 - 15,20
piece_2 - 20050731 - 2,75
Piece_3 - 20050803 - 5,00

Et j'ai besoin du résultat suivant :

piece_1 - 20050901 - 15,50
piece_2 - 20050810 - 2,90
Piece_3 - 20050815 - 5,10

Merci d'avance

Fred_76

[Strab]

Je ne suis pas un pro, et je n'ai pas de base sous la main pour tester.
Mais je tenterais quelque chose du genre :

Code :

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

1
2
 
select * from historique where date_der_mouv=max(date_der_mouv);

[pcaboche]

Non, ça ne peut pas marcher. Pour utiliser MAX, il faut qu'il y ait un GROUP BY (idem pour SUM, COUNT, etc.)

Essaye un truc du genre:

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT 
  stock.ref, 
  MAX(historique.date_der_mouv) AS date_mouv,
  historique.valeur
 
FROM
  historique JOIN
  stock ON historique.id_stock = stock.id_stock
 
GROUP BY historique.id_stock

Si ça marche, merci à Strab pour m'avoir mis sur la voie.


PS: Au fait Strab, j'adore ta signature: moi aussi je pense que tout le monde devrait connaitre OCaml !

[Fred_76]

Bonjour,

Merci pour vos réponses : ça marche !!!

MAIS, malheureusement, le GROUP BY me fait perdre le prix correspondant à la date sélectionné...il me retourne 0 ???

Une idée ?

Merci d'avance

Fred

[pcaboche]

Tu as raison, dans la requête précédente le prix sélectionné n'est pas le bon.

Ce que tu peux faire, c'est créer une table temporaire que tu remplis avec id_historique, date_der_mouv (tu fais un GROUP BY sur id_stock et un MAX sur date_der_mouv), et après, tu fais une deuxième requête avec une jointure entre ta table temporaire, ton historique (pour récupérer la valeur) et ton stock (pour récupérer la référence). Enfin tu n'oublies pas de vider (ou détruire) ta table temporaire.

Attention quand même, car lorsque tu ferras ta jointure, tu risques d'avoir des doublons si dans ton historique tu peux avoir un produit qui a deux valeurs différentes à une même date. (en effet, je suppose que ta clef primaire pour historique est id_historique, et non le couple (id_stock, date_der_mouv), n'est-ce pas?).

Attention aussi aux accès concurrents à ta table temporaire.

J'ai essayé avec des requêtes imbriquées, mais je n'y suis pas arrivé. Peut-être que quelqu'un d'autre aura su le faire.

[Fred_76]

Salut pcaboche..

Et merci de l'interêt porté à ma question...

La table temporaire et la jointure, j'y avais pensé....
Le problème c'est que ma requête ne me retourne ni le prix ni le id_historique non plus !!! (puisque le GROUP est fait sur id_stock)
Elle me retourne le id_part et le date_der_mov

(Pour le problème des doublons aucun risque car le champs date_der_mov contient date + time à la seconde pres...j'avais simplifié pour l'exemple)

Alors je sèche complètement....je me demande tout simplement si c'est possible....

N'hésite pas si tu as d'autres idées...

Merci et A+
Fred

[pcaboche]

selecta a posté quelque chose d'intéressant sur un autre sujet: http://www.developpez.net/forums/vie...10771&start=15

peux tu essayer cette syntaxe

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select C.*
from comptage C,
(
SELECT numero, ligne, MAX(date_lecture) date_lecture FROM comptage
group by numero, ligne
) M
where M.numero = C.numero
and M.ligne = C.ligne
and M.date_lecture = C.date_lecture

fonctionne à partir de la 4.1.x

Malheureusement, je ne peux pas la tester, ma version de MySQL est un peu trop ancienne. C'est peut-être pour cela que je n'arrivais pas à faire certaines requêtes imbriquées! (NOTE A MOI-MEME: penser à mettre à jour! )

Sinon, c'est quoi ta version de MySQL? (en fait, mon ancienne version de MySQL me permet de résoudre certains problèmes de rétrocompatibilité)

[Fred_76]

Ma version est la 4.0.20....

Je vais tester cela et te tiens au courant....

Merci

[pcaboche]

Je doute que ça marche si t'es pas au moins en 4.1.x ...

Donc il te reste la solution avec table temporaire. En principe, tu devrais pouvoir la remplir avec au moins historique.id_stock et MAX(historique.date_der_mouv) en modifiant un peu la requête SQL. Ensuite, avec quelques jointures bien choisies, tu devrais t'en sortir.

[Fred_76]

Oui...ou faire un upgrade de mon MySql.

Si je suis certain qu'avec une requête cela fonctionne, je ferai l'upgrade...

Merci

Fred

[pcaboche]

Si tu n'as pas le problème du "mon hébergeur ne me propose que du MySQL 4.0.x", alors la solution de l'upgrade est très probablement la plus judicieuse en effet.

[Fred_76]

Je n'arrive décidemment pas à trasnposer cette requête du select dans le select à mon problème.....

En effet, dans cet exemple, il y a 3 liens qui permettent de retouver les enregistrements correspondants...moi je n'ai que le id_stock qui est retourné par le deuxième select ????

[pcaboche]

Ton problème me donne du fil à retordre, mais je ne l'ai pas complètement oublié !

Je veux bien tenter quelque chose, mais je n'ai pas les moyens de tester la requête avec ma version de MySQL (et je n'ai pas le temps de mettre à jour).

On aurait une requête du genre:

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT A.*
FROM stock S
JOIN historique A ON A.id_stock=S.id_stock
 
WHERE A.id_historique=
 (SELECT B.id_historique
  FROM historique B
  WHERE B.id_stock=S.id_stock
  ORDER BY B.date_der_mouv DESC
  LIMIT 1)

Ca m'étonnerais que ça marche du premier coup, mais si ça peut inspirer quelqu'un, tant mieux !

(à priori, ce n'est même pas la peine d'essayer ça si on n'est pas au moins en MySQL >= 4.1.x)

[Fred_76]

Bonjour,

Je te remercie de l'interêt porté à mon problème et j'ai de nouveaux éléments :

Je ré-essayer une requête simple, comme celle à laquelle j'avais pensé en premier lieu car si je peux éviter le SELECT dans un SELECT, je préfère :

Voilà ma requête :

SELECT historique.id_stock,
MAX(historique.move_date) AS move_date,
stock.reference,
historique.price
FROM
historique JOIN
stock ON historique.id_stock = stock.id_stock
GROUP BY historique.id_stock

Voilà les données introsuites dans le fichier historique :

# Dumping data for table `historique`
#

INSERT INTO historique (id_stock, move_date, price) VALUES (1,20040901,'60.15');
INSERT INTO historique (id_stock, move_date, price) VALUES
(2,20040912,'15');
INSERT INTO historique (id_stock, move_date, price) VALUES
(1,20041104,'60.4');
INSERT INTO historique (id_stock, move_date, price) VALUES
(3,20040831,'2.85');
INSERT INTO historique (id_stock, move_date, price) VALUES
(1,20041206,'60');
INSERT INTO historique (id_stock, move_date, price) VALUES
(2,20040911,'15');
INSERT INTO historique (id_stock, move_date, price) VALUES
(2,20041023,'15.15');
INSERT INTO historique (id_stock, move_date, price) VALUES
(1,20050117,'60.65');
INSERT INTO historique (id_stock, move_date, price) VALUES
(3,20050117,'2.95');
INSERT INTO historique (id_stock, move_date, price) VALUES
(3,20050226,'2.95');


Et voilà le résultat retourné :

1 20050117 1234 60.15
2 20041023 4567 15
3 20050226 9876 2.85

J'obtiens bien mes id, la date maximum pour chaque référence YOUPI !!!
(le premier problème des id et prix non retourné était du à un interface..)

MAIS

Les prix retournés correspondent à la PREMIERE ligne de référence de la table historique et NON le prix qui correspond à LA DATE RETOURNEE

En effet, j'aurais du obtenir ceci :

1 20050117 1234 60.65
2 20041023 4567 15,15
3 20050226 9876 2.95


??????

Y voyez vous une explication ? une erreur de requête ? une faiblesse MySql ?

Si quelqu'un sait....

Merci

[pcaboche]

Bon, quelques petites précisions:

1) J'ai fait un petit tour dans la doc de MySQL et j'ai découvert que le GROUP BY n'était pas nécessaire pour faire un MAX (contrairement à ce que je pensais. Mea culpa)

2) Toujours dans la doc MySQL, j'ai trouvé ceci:

Task: For each article, find the dealer or dealers with the most expensive price.

In standard SQL (and as of MySQL 4.1), the problem can be solved with a subquery like this:

Code :

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

1
2
3
4
5
SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

Donc il n'y a rien de choquant à faire une SELECT dans un SELECT. Il faut juste s'assurer d'être au moins en MySQL 4.1


3) Il vaut mieux faire une recherche du MAX (complexité O(n)), plutôt qu'un ORDER BY ... DESC LIMIT 1 (complexité O(n*n)). Cependant, dans ma requête, j'ai préféré faire une comparaison sur des id_historique que sur des dates (même si dans ton cas, ça ne devrait pas poser de problème)

Donc en théorie, on pourrait faire ça:

Code :

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

1
2
3
4
5
6
7
8
SELECT A.*, S.*
FROM stock S
JOIN historique A ON A.id_stock=S.id_stock
 
WHERE A.date_der_mouv=
 (SELECT MAX(B.date_der_mouv)
  FROM historique B
  WHERE B.id_stock=S.id_stock)

... mais j'aime moins.

Comme dirait Donald P. Knuth, quand on développe une application, il faut (dans l'ordre):
1) faire en sorte que ça marche
2) faire en sorte que ça marche bien
3) faire en sorte que ça marche vite

Toujours selon Knuth: "Premature optimization is the root of all evil"


Sinon, pour répondre à ta question:

Les prix retournés correspondent à la PREMIERE ligne de référence de la table historique et NON le prix qui correspond à LA DATE RETOURNEE

Oui, c'est tout à fait normal.

Essaye d'abord la requête précédente (faire pour que ça marche bien), ensuite on essayera quelque chose de mieux avec une jointure réflexive (faire pour que ça marche vite)

EDIT:

Un truc du genre:

Code :

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

1
2
3
4
5
6
7
8
9
SELECT
  B.id_stock, B.move_date, S.reference, B.price
 
FROM
  historique A
  JOIN historique B ON B.move_date=MAX(A.move_date)
  JOIN Stock S ON S.id_stock=B.id_stock
 
GROUP BY A.id_stock

(par contre, c'est pas dit que ça marche...)

[Fred_76]

Bonjour,

Tout d'abord merci pour le temps consacré à mon problème....

Je du laisser cette requête en Stand by pour le moment et j'installerai la version 4.1 dès que j'ai le temps pour tester ce SELECT dans le SELECT.

En tout cas je retiens ce théorème malicieux de Donald P. Knuth.... il me servira j'en suis certain....

Je vous tiens au courant dans tous les cas

Merci et a bientot

Fred

[pcaboche]

Tout d'abord merci pour le temps consacré à mon problème....

Pas de quoi: je suis sûr que tu n'es pas le seul à avoir été confonté à ce genre de problème (récupérer la dernier ligne d'un historique pour chaque article), donc je me dit que ça pourra resservir quelqu'un un jour.

En tout cas je retiens ce théorème malicieux de Donald P. Knuth.... il me servira j'en suis certain....

Monsieur Knuth est un peu une référence en matière d'informatique (voir sa biographie: http://en.wikipedia.org/wiki/Donald_Knuth)

Erratum:
C'est: Donald E. Knuth (Mea culpa)