[SQL] Max en Datum bij een GROUP BY

[dik_voormekaar]

Ik heb in MySQL een tabel met beurskoersen.
Per fonds en datum is er een koers.
Ik wil een query doen waarbij per fonds de maximum koers en de bijbehorende datum wordt weergegeven. Dit moet natuurlijk met een group by.

Ik heb de volgende query:

SQL:

SELECT fonds, datum, max(koers)
FROM koersen 
GROUP BY fonds

Het probleem is hier dat de datum die terug gegeven wordt, de eerste datum is en niet de datum van het record met max(koers).
Als ik max(datum) gebruik, dan wordt de laatste datum weergegeven.
Hoe kan ik dit oplossen?

[P_de_B]

Leesvoer: P&W FAQ - SQL

Als je GROUP BY gebruikt moet je iedere regel in je select opnemen in de GROUP BY regel, of je moet er een aggregate functie (zoals max, sum etc.) op toepassen.

Dat MySQL het toestaat dat dit niet gebeurd is belachelijk, en leidt tot dit soort fouten.

[TeeDee]

Volgens de SQL standaard dien je bij een GROUP BY ook de te selecteren velden (dus fonds, datum max(koers)) te plaatsen. MySQL doet dit 'fout'.

edit:
Blargh... P_de_B

[ Voor 10% gewijzigd door TeeDee op 28-03-2008 14:37 ]

[dik_voormekaar]

TeeDee schreef op vrijdag 28 maart 2008 @ 14:36:
Volgens de SQL standaard dien je bij een GROUP BY ook de te selecteren velden (dus fonds, datum max(koers)) te plaatsen. MySQL doet dit 'fout'.

edit:
Blargh... P_de_B

Ok, ik mag dus eigenlijk niet datum in de select zetten als die niet in de group by zit, en er geen aggregate functie op zet.
Dan kan mijn probleem dus niet opgelost worden?

[TeeDee]

Jawel, om te beginnen even lezen hoe een group by werkt (zie FAQ link van P_de_B).

[dik_voormekaar]

TeeDee schreef op vrijdag 28 maart 2008 @ 14:48:
Jawel, om te beginnen even lezen hoe een group by werkt (zie FAQ link van P_de_B).

Dat heb ik gedaan, en daar staat ook in wat ik zonet gezegd heb. Het datum veld mag niet want die staat niet in de group-by en er is geen aggregate functie gebruikt.
De aggregate functies min(datum) of max(datum) geven niet het gewenste resultaat, omdat ik zoek naar de datum die hoort bij het record van max(koers).

[Icelus]

dik_voormekaar schreef op vrijdag 28 maart 2008 @ 14:50:
[...]

Dat heb ik gedaan, en daar staat ook in wat ik zonet gezegd heb. Het datum veld mag niet want die staat niet in de group-by en er is geen aggregate functie gebruikt.
De aggregate functies min(datum) of max(datum) geven niet het gewenste resultaat, omdat ik zoek naar de datum die hoort bij het record van max(koers).

Je dient hierbij een zgn. Groupwise Maximum constructie te gebruiken.

---

P_de_B schreef op vrijdag 28 maart 2008 @ 14:35:
Leesvoer: P&W FAQ - SQL

Als je GROUP BY gebruikt moet je iedere regel in je select opnemen in de GROUP BY regel, of je moet er een aggregate functie (zoals max, sum etc.) op toepassen.

Dat MySQL het toestaat dat dit niet gebeurd is belachelijk, en leidt tot dit soort fouten.

Mee eens.
Zie ook: Debunking the Group By Myth

[ Voor 33% gewijzigd door Icelus op 28-03-2008 15:11 ]

[dik_voormekaar]

Ik heb het kunnen oplossen door een view te maken:

SQL:

create view koersmax as 
select id,max(koers) as high from koersen group by id

en vervolgens:

SQL:

select * from koersen as k join koersmax as m on (k.id=m.id and k.koers=m.high)

Ik ga ook nog kijken of de groupwise max werkt.

[dik_voormekaar]

Icelus schreef op vrijdag 28 maart 2008 @ 15:09:
[...]
Je dient hierbij een zgn. Groupwise Maximum constructie te gebruiken.

Wow, die werkt ook!

De oplossing hier was:

SQL:

SELECT id, datum, koers
FROM   koersen k1
WHERE  koers=(SELECT MAX(k2.koers)
              FROM koersen k2
              WHERE k1.id = k2.id)

Heb ik dus die view niet nodig.