Hallo Mark,
das mit den verschiedenen Rechenfaktoren habe ich nicht verstanden und weggelassen. Der Query ermittelt jetzt, getrennt in drei einzelnen SUB-Select, die MAX-Werte für L, M, N und liefert in zwei weiteren Spalten den größten Wert und die "Art" um welchen Wert es sich eigentlich handelt.
SELECT max_l.id_company, max_l.resultDate, max_l.l, max_m.m, max_n.n,
GREATEST(max_l.l, max_m.m, max_n.n) AS max_value,
CASE
WHEN GREATEST(max_l.l, max_m.m, max_n.n) = max_l.l THEN 'L'
WHEN GREATEST(max_l.l, max_m.m, max_n.n) = max_m.m THEN 'M'
WHEN GREATEST(max_l.l, max_m.m, max_n.n) = max_n.n THEN 'N'
ELSE NULL
END AS max_art
FROM ( SELECT
id_company,
resultDate,
MAX(L) AS L
FROM results
WHERE resultDate <= '2011-12-15' AND resultDate > ('2011-12-15'- INTERVAL 3 DAY)
GROUP BY id_company,resultDate
) max_l
LEFT JOIN ( SELECT
id_company,
resultDate,
MAX(M) AS M
FROM results
WHERE resultDate <= '2011-12-15' AND resultDate > ('2011-12-15'- INTERVAL 3 DAY)
GROUP BY id_company,resultDate
) max_m
ON max_m.id_company = max_l.id_company
AND max_m.resultDate = max_l.resultDate
LEFT JOIN ( SELECT
id_company,
resultDate,
MAX(N) AS N
FROM results
WHERE resultDate <= '2011-12-15' AND resultDate > ('2011-12-15'- INTERVAL 3 DAY)
GROUP BY id_company,resultDate
) max_n
ON max_n.id_company = max_l.id_company
AND max_n.resultDate = max_l.resultDate
ORDER BY max_l.id_company;
+------------+------------+---------+---------+--------+-----------+---------+
| id_company | resultDate | l | m | n | max_value | max_art |
+------------+------------+---------+---------+--------+-----------+---------+
| 331 | 2011-12-15 | 0.2200 | 0.0000 | 0.0000 | 0.2200 | L |
| 501 | 2011-12-14 | 4.5200 | 12.4900 | 1.3400 | 12.4900 | M |
| 562 | 2011-12-14 | 63.7400 | 0.0000 | 0.0000 | 63.7400 | L |
| 685 | 2011-12-13 | 5.1800 | 0.0000 | 0.0000 | 5.1800 | L |
| 693 | 2011-12-15 | 0.6500 | 0.0000 | 5.6300 | 5.6300 | N |
+------------+------------+---------+---------+--------+-----------+---------+
5 rows in set (0.00 sec)
mysql>
Ich hoffe, die "Richtung" stimmt einigermassen.
Grüße
Thomas