[MySQL] problemen met GROUP BY en MAX

[marty]

dump van m'n data

code:

FactorID NameID  Value       DateFrom       DateTo
1        1       1.7095      2003-01-01     2003-12-31
2        2       1.7907      2003-01-01     2003-12-31
3        3       1.6669      2003-01-01     2003-12-31
4        4       1.6669      2003-01-01     2003-12-31
5        5       1.26        2003-01-01     2003-12-31
6        6       0.6         2003-01-01     2003-12-31
7        7       3.5         2003-01-01     2003-12-31
8        8       10.48       2003-01-01     2003-12-31
9        9       8           2003-01-01     2003-12-31
10       10      19          2003-01-01     2003-12-31
11       11      NULL        2003-01-01     2003-12-31
12       12      NULL        2003-01-01     2003-12-31
13       13      NULL        2003-01-01     2003-12-31
14       14      NULL        2003-01-01     2003-12-31
15       1       2.7095      2004-01-01     2004-02-01
16       2       2.7907      2004-01-01     NULL
17       3       2.6669      2004-01-01     NULL
18       4       2.6669      2004-01-01     NULL
19       5       2.26        2004-01-01     NULL
20       6       1.6         2004-01-01     NULL
21       7       4.5         2004-01-01     NULL
22       8       11.48       2004-01-01     NULL
23       9       9           2004-01-01     NULL
24       10      20          2004-01-01     NULL

Doel: Ik wil de rows selecteren met de hoogste DateFrom, waarbij er gegroepeerd moet worden op NameID.
Het lijkt zo makkelijk, maar ben nu toch al weer een uur bezig, en wil maar niet lukken.
Ik heb zo'n beetje alle vormen van GROUP BY & Having gehad, zitten combineren met INNER en LEFT joins, maar het lukt niet.
Wat nog het dichts in de buurt kwam was deze query:

MySQL:

SELECT F1.NameID, MAX(F1.DateFrom) AS maxdate, F2.FactorID
FROM Factor AS F1
    INNER JOIN Factor AS F2 ON F2.DateFrom=F1.DateFrom AND F2.NameID=F1.NameID
GROUP BY F1.NameID

Dat is 'm bijna, het enige wat ik hier aan zou moeten wijzigen is in die INNER JOIN dit doen: F2.DateFrom=MAX(F1.DateFrom), maar dat mag uiteraard weer niet van MySQL. En subqueries mogen ook nog niet .. (heb nog geen ver >= 4.1)

De query die ik in eerste instantie gemaakt had is deze:

MySQL:

SELECT
    F.*, MAX(F.DateFrom)
FROM Factor AS F
GROUP BY F.NameID HAVING F.DateFrom=MAX(F.DateFrom)

Die ziet er naar mijn idee nog het beste uit, maar daarbij krijg ik alleen de records met FactorID 11 tm 14 terug. Waarschijnlijk omdat de records met een gelijk NameID en een hoger DateFrom ontbreken. Ik heb dat getest door de data aan te vullen met 4 extra records en dan levert deze query inderdaad niets meer op.
Dit vat ik ook niet helemaal

Heb ook nog geprobeerd de datum hard in te voeren:

MySQL:

SELECT F.FactorID, F.NameID, F.DateFrom, F.Value
FROM Factor AS F
GROUP BY F.NameID HAVING F.DateFrom >= '2004-01-01'

Dit levert ook noppes op. Als ik echter van de datum '2003-01-01' of '2002-12-31' maak, dan krijg ik de eerste 14 records. Maak ik er '2003-01-02' van dan krijg ik weer niets.

schiet mij maar lek...

Ik heb dit overigens op een 3.x en een 4.0.13 versie getest

Iemand enig idee hoe ik deze eenvoudig ogende bewerking voor elkaar krijg?
En kan iemand mij misschien ook de logica uitleggen waarom die 2e en 3e query niet werken? (dat die eerste niet werkt snap ik wel)

[justmental]

Ik ken geen MySQL truuk die je hiermee onder een subquery uit helpt, wat de ge-eigende methode is in dit geval.
Wellicht kun je in de programmatuur waar je het SQL in gebruikt iets uithalen in de trant van:
SQL: order by name asc , date desc
prog: loop fetch, if name!=vorige name then tonen end loop

[faabman]

volgens mij moet dit je probleem kunnen oplossen...

http://www.mysql.com/doc/...mum-column-group-row.html

[google=selecting max values]

[bigbeng]

Volgens mij is het antwoord redelijk simpel. Je moet alleen vanuit SQL redeneren naar je probleem toe ipv andersom. Klinkt een beetje vaag dus laat het me uitleggen.

Wat je wil is de rij met de maximale datum per nameId (toch?). Normaal zou je dat doen met een query die er als volgt uitziet:

code:

SELECT nameId, MAX(datumFrom)
FROM factor
GROUP BY nameId

Maar, jij wilt meer. Jij wil ook andere velden zien uit de tabel.

Dus nu moet je ervoor gaan zorgen dat je buitenste (of bovenste) select statement geen aggregate functie (MAX,AVG, etc...) meer bevat. Waarom? Nou alle extra velden die je uit de tabel wilt halen moeten dan voorkomen in je GROUP BY, dat is nu eenmaal de regel. Helaas verandert dan ook het gedrag van je query en dat is niet de bedoeling.

Deze query zou het voor je moeten doen:

code:

SELECT nameId, datumFrom, datumTo
FROM factor f1
WHERE datum IN (
   (SELECT MAX(datumFrom) FROM factor f2 WHERE f2.nameId=f1.nameId
)

Ik werk hier met een zogenaamde subquery. Het nadeel hiervan is dat de performance nogal eens tegenvalt. Het grote voordeel is dat wanneer jij besluit om nog meer velden uit factor te halen om te tonen, dat je deze gewoon toe kan voegen aan de bovenste select, zonder dat dit invloed uitoefent op je query.

Ik hoop dat de query werkt voor je, ik doe hem uit mijn hoofd dus ik kan niet aansprakelijk worden gesteld voor typos en andere domme fouten

[marty]

bigbeng schreef op 30 januari 2004 @ 18:51:
Volgens mij is het antwoord redelijk simpel. Je moet alleen vanuit SQL redeneren naar je probleem toe ipv andersom. Klinkt een beetje vaag dus laat het me uitleggen.

[... knip ...]

Ik werk hier met een zogenaamde subquery.

marty schreef op 30 januari 2004 @ 18:00:
En subqueries mogen ook nog niet .. (heb nog geen ver >= 4.1)

ik schrijf niet voor niets zo'n volledig verhaal!

FvKnijff schreef op 30 januari 2004 @ 18:20:
volgens mij moet dit je probleem kunnen oplossen...

http://www.mysql.com/doc/...mum-column-group-row.html

Sjees...wat een omslachtig gedoe zeg, om met temporary tables aan de slag te moeten voor zoiets basaals. Nouja, ´t moet maar.

Maar, blijft er nog 1 vraag staan: waarom lukken die 2e en 3e query uit m´n openingspost niet? Als ik over GROUP BY en HAVING enzo lees, dan zou die query gewoon moeten werken volgens mij. Maar niet dus, ... ben gewoon benieuwd waar ik de fout in ga

[ Voor 41% gewijzigd door marty op 30-01-2004 19:48 ]

[bigbeng]

marty schreef op 30 januari 2004 @ 19:36:
[...]


[...]


ik schrijf niet voor niets zo'n volledig verhaal!

Oeps Te snel gelezen. Wacht ff, ik herschrijf em ff met alleen joins. Maar geef me even, ben ook aan het koken

edit:

Dit heb ik eruit gekregen, ik weet niet goed of ik trots moet zijn.
Ik heb het getest met een kleine tabel, waarbij het leek te werken, maar ik kan niet zeggen of het echt werkt.
Ik heb trouwens even gecontroleerd met de 3.23 manual en de EXISTS clause wordt daar al in genoemd.

Maar goed, de oplossing (hoop ik):

code:

SELECT t1.nameId, t1.dateFrom, t1.dateTo
FROM factor t1
WHERE EXISTS (
   SELECT MAX(t2.dateFrom)
   FROM factor t2
   WHERE t1.nameId = t2.nameId
   GROUP BY t2.nameId
   HAVING t1.dateFrom = MAX(t2.dateFrom)
)

Ik hoor graag nog van U...

[ Voor 53% gewijzigd door bigbeng op 30-01-2004 21:56 ]

[marty]

bigbeng schreef op 30 ;) j 8)7 a 8)7 nuari 2004 @ 20:40:
[...]


Oeps Te snel gelezen. Wacht ff, ik herschrijf em ff met alleen joins. Maar geef me even, ben ook aan het koken

edit:

Dit heb ik eruit gekregen, ik weet niet goed of ik trots moet zijn.
Ik heb het getest met een kleine tabel, waarbij het leek te werken, maar ik kan niet zeggen of het echt werkt.
Ik heb trouwens even gecontroleerd met de 3.23 manual en de EXISTS clause wordt daar al in genoemd.

Maar goed, de oplossing (hoop ik):

code:

SELECT t1.nameId, t1.dateFrom, t1.dateTo
FROM factor t1
WHERE EXISTS (
   SELECT MAX(t2.dateFrom)
   FROM factor t2
   WHERE t1.nameId = t2.nameId
   GROUP BY t2.nameId
   HAVING t1.dateFrom = MAX(t2.dateFrom)
)

Ik hoor graag nog van U...

ehhh...hoe kan ik dit nog duidelijker zeggen...

je ne peux pas faire des subqueries
ich kann nicht subqueries tun
non posso fare i subqueries
no puedo hacer subquerios
I CAN`T DO SUBQUERIES !!!

[BrZ]

marty schreef op 30 januari 2004 @ 19:36:
Maar, blijft er nog 1 vraag staan: waarom lukken die 2e en 3e query uit m´n openingspost niet? Als ik over GROUP BY en HAVING enzo lees, dan zou die query gewoon moeten werken volgens mij. Maar niet dus, ... ben gewoon benieuwd waar ik de fout in ga

Omdat je HAVING verkeerd gebruikt
Je kan namelijk niet een bepaald veld aanspreken, zoals jij doet, bv. DateFrom, je kan enkel een aggregatie functie gebruiken. Dit komt omdat je een GROUP BY doet, waardoor er niet 1 waarde is, maar meerdere, en met HAVING kan je een voorwaarde stellen op de uitkomst van een aggregatie functie.

[bigbeng]

Nevermind...

[ Voor 98% gewijzigd door bigbeng op 31-01-2004 17:44 ]