[MySQL] GROUP BY + laatste datum probleem

[Icelus]

Volgens mij een simpel probleem maar na een tijd zoeken nog steeds niet gevonden.

Ik heb een tabel met daarin o.a. een 'thread', 'datum' en 'bericht' kolom.
Er zijn diverse berichten die als thread gebundeld zijn. Ik wil nu van iedere thread de laatste datum en bijbehorende bericht.

code:

SELECT thread, datum, bericht, MAX( datum ) AS maxdatum 
FROM log
GROUP BY thread

Dit levert inderdaad van iedere thread één rij op en de laatste datum (maxdatum) van ieder bericht klopt ook.

Het veranderen van de query in:

code:

SELECT thread, datum, bericht, MAX( datum ) AS maxdatum
FROM log
GROUP BY thread HAVING datum=maxdatum

Dit 'werkt' maar geeft alleen de threads terug waarin één bericht voorkomt. Het lijkt dat het grouperen niet goed gaat en alleen threads met één bericht doorgelaten worden aangezien alleen hier de datum gelijk is aan de hoogste datum.

Weet iemand of wat ik wil mogelijk is en hoe de query aangepast zou moeten worden?

[justmental]

Having is om groepen uit te sluiten.
Je hebt een subquery nodig.

[NMe]

Volgens mij moet je altijd groupen op elk veld dat geen aggregated functie is, dus

code:

GROUP BY thread, datum, bericht

Maar dat zal je probleem (denk ik) niet oplossen, misschien dat het inderdaad met een subquery wel wil lukken. Al is me nog niet helemaal duidelijk wat je nou bij je eerste query voor waarde krijgt, en wat hier dan niet goed aan is.

[Icelus]

NMe84 schreef op 04 juni 2004 @ 11:07:
Volgens mij moet je altijd groupen op elk veld dat geen aggregated functie is, dus

code:

GROUP BY thread, datum, bericht

Maar dat zal je probleem (denk ik) niet oplossen, misschien dat het inderdaad met een subquery wel wil lukken. Al is me nog niet helemaal duidelijk wat je nou bij je eerste query voor waarde krijgt, en wat hier dan niet goed aan is.

Bij de eerste query krijg ik voor iedere thread één rij terug maar niet het laatste bericht uit die thread. Meestal krijg ik het eerste bericht van een thread terug (qua datum) maar soms ook een ander bericht uit een thread.

Meerdere velden aan de GROUP BY toevoegen geeft meerdere (alle?) rijen per thread terug terwijl ik alleen het laatste bericht (met hoogste datum) per thread wil.

[ixi]

Misschien "ORDER BY datum DESC" erachter gooien?

[Icelus]

ixi schreef op 04 juni 2004 @ 11:30:
Misschien "ORDER BY datum DESC" erachter gooien?

ORDER BY wordt toch pas na GROUP BY uitgevoerd?

[bigtree]

Icelus schreef op 04 juni 2004 @ 11:38:
[...]
ORDER BY wordt toch pas na GROUP BY uitgevoerd?

Dat ligt er aan of je sorteert op een tabel-kolom of op het resultaat van een group-by functie.

[cameodski]

bigtree schreef op 04 juni 2004 @ 16:13:
[...]
Dat ligt er aan of je sorteert op een tabel-kolom of op het resultaat van een group-by functie.

Nee, daar ligt dat helemaal niet aan. Als je MAX (datum) as MaxDatum doet, kun je echt niet sorteren op datum, maar wel op MAX (datum).
Alles wat in je select list teruggeeft of wat je gebruikt in de order by moet of in de group by staan of het moet een aggregate (max, min, enz..) zijn.
MySQL slikt helaas meer, dus dat zul je zelf moeten controleren om problemen te voorkomen.

@Icelus: je zult inderdaad een subquery moeten gebruiken of nog beter een derived table, maar ik weet niet of jouw versie al subqueries en derived tables ondersteund.

[bigtree]

cameodski schreef op 04 juni 2004 @ 16:40:
[...]

Nee, daar ligt dat helemaal niet aan. Als je MAX (datum) as MaxDatum doet, kun je echt niet sorteren op datum, maar wel op MAX (datum).

Ander voorbeeld dan: je hebt een tabel met gedateerde entries en je wilt het aantal entries per week en jaar weten:

SQL:

SELECT DATE_FORMAT(datumkolom, '%u, %Y') AS weekjaar, COUNT(*) AS aantal 
FROM tabel GROUP BY weekjaar ORDER BY datumkolom

Als je zou sorteren op weekjaar komen de jaren door elkaar heen te staan. Die volgorde kan mysql trouwens pas bepalen NA de GROUP BY. Als je sorteert op datumkolom wordt dat gedaan VOOR de GROUP BY.

[cameodski]

bigtree schreef op 04 juni 2004 @ 17:14:
[...]

Ander voorbeeld dan: je hebt een tabel met gedateerde entries en je wilt het aantal entries per week en jaar weten:

SQL:

SELECT DATE_FORMAT(datumkolom, '%u, %Y') AS weekjaar, COUNT(*) AS aantal 
FROM tabel GROUP BY weekjaar ORDER BY datumkolom

Als je zou sorteren op weekjaar komen de jaren door elkaar heen te staan. Die volgorde kan mysql trouwens pas bepalen NA de GROUP BY. Als je sorteert op datumkolom wordt dat gedaan VOOR de GROUP BY.

Twee mogelijkheden:
1. Je hebt het niet getest.
2. MySQL is knettergek (gebruik eventueel een fatsoenlijk RDBMS om dit te testen).

Stel: we hebben de datums 03-06-2004 en 04-06-2004. Als je dit gaat groeperen op week en daarna op jaar, krijg je dus in week 22 van 2004 één record terug waar het aantal op twee staat.
Sorteren op datum kan dan niet meer, omdat het RDBMS niet weet welke van de twee datums er gebruikt moet worden.

Eerst sorteren is zinloos en dat zal een RDBMS dan ook NOOIT doen, omdat de datum tijdens de group by alleen maar in de weg zit. Het feit dat MySQL een dergelijke constructie wel slikt, is erg jammer, omdat het bugs produceert en gewoonweg nergens op slaat.

[Icelus]

@Icelus: je zult inderdaad een subquery moeten gebruiken of nog beter een derived table, maar ik weet niet of jouw versie al subqueries en derived tables ondersteund.

Ik kan geen subqueries gebruiken; derived tables weet ik niet maar waarschijnlijk ook niet.

[NMe]

cameodski schreef op 04 juni 2004 @ 18:04:
Twee mogelijkheden:
1. Je hebt het niet getest.
2. MySQL is knettergek (gebruik eventueel een fatsoenlijk RDBMS om dit te testen).

Over punt 2: zie dit linkje uit de sig van ACM. MySQL is idd knettergek. Ik ben vaker aangelopen tegen "fouten" in Access na het coverteren van MySQL naar een Accessdatabase, terwijl achteraf bleek dat ik ranzige SQL gebruikt had, die MySQL gewoon pikt... En we weten allemaal hoe lastig het is foute gewoontes af te leren...

[Icelus]

Denk dat ik 'm gevonden heb:

code:

SELECT l1.thread, l1.datum, l1.bericht
FROM log l1
INNER JOIN log l2
  ON l1.thread = l2.thread
GROUP BY l1.thread, l1.bericht
HAVING l1.datum = MAX( l2.datum ) 
ORDER BY datum DESC

Deze query geeft de correcte rijen terug.
Kan dit nog eenvoudiger?

[NMe]

Volgens mij niet. Maar waarom zou je hem eenvoudiger willen hebben als hij werkt? Het is een enkelvoudige query dus snel issie sowieso wel.