[mysql] snap resultaat van group by en max niet

[marty]

al weer een vraag, gaat lekker vandaag....

Een bepaalde actie kan meerdere keren voorkomen, en om nu ook bij te houden hoe vaak een user zo'n actie doet, wil ik er overal een insert van maken, ipv de vorige actie te updaten.
Echter, bij het weergeven van de data van zo'n user wil ik de row uit m'n tabel, die het laatst is ingevoerd.

m'n tabel ziet er als volgt uit:

id (auto_increment)
user_id
veld1
veld2
veld3
tijd (timestamp)

Nu doe id

code:

SELECT id, veld1, veld2 MAX(tijd) AS tijd
GROUP BY user_id

Nou zou ik dus verwachten dat als er van user_id=3, drie rows voorkomen, dat ie de row met de hoogste timestamp pakt. Maar wat doet mysql tot mijn verbasing? Hij pakt alle data uit de de eerste row (met de laagste timestamp) en zet vervolgens in het veld van de timestamp wel de hoogste timestamp neer.

dus stel die 3 rows zijn
id | user_id | veld1 | veld2 | tijd
-----------------------------------
1 | 3 | bla | dibla | 10
2 | 3 | een | twee | 12
3 | 3 | php | java | 14

dan krijg ik als voor user_id 3 terug:
1 | 3 | bla | dibla | 14

hoe kan dat?

[ Voor 64% gewijzigd door marty op 20-12-2002 23:21 ]

[Apollo_Futurae]

dat is volkomen logisch.
wat je wilt is

code:

SELECT id, veld1, veld2 MAX(tijd) AS tijd
WHERE tijd = MAX(tijd)
GROUP BY user_id

toevoeging: het is erg onhandig om een expressie de naam van een bestaande kolom te geven ('MAX(tijd) AS tijd'); bij deze query maakt het niet uit, maar het wordt veel duidelijker en minder ambigu door het een eigen naam te geven, als in

code:

SELECT id, veld1, veld2 MAX(tijd) AS maxtijd
WHERE tijd = maxtijd
GROUP BY user_id

[ Voor 55% gewijzigd door Apollo_Futurae op 20-12-2002 23:28 ]

[Janoz]

Apollo.. Close, but no sigar..

Op dit moment probeer je verschillende records met elkaar te vergelijken wat niet zomaar gaat werken.. De topicstarter is meer op zoek naar het HAVING onderdeel van het select statement. Check de manual maar ff. HAVING time = MAX(time) zou ongeveer moeten werken.

[Apollo_Futurae]

Janoz schreef op 20 december 2002 @ 23:40:
Apollo.. Close, but no sigar..

zonde

[marty]

Dat vergat ik nog te zeggen, ik heb ook met HAVING zitten klooien maar dat gaf nog vagere resultaten
Als ik dat HAVING time = MAX(time) toevoeg dan krijg ik slechts 1 row terug, en helemaal niet waar ik om vraag.

even m'n voorbeeld uitbreiden:

id | user_id | veld1 | veld2 | tijd
-----------------------------------
1 | 1 | bla | dibla | 5
2 | 3 | bla | dibla | 10
3 | 3 | een | twee | 12
4 | 3 | php | java | 14

(staat nu dus ook een user_id met 1 in, helemaal bovenaan)
MET die HAVING time = MAX(time) krijg ik nu alleen nog maar die row met id=1 terug

edit
vaag... ik dacht, voor de grap eens kijken wat MIN dan doet: Die geeft wel (terecht) 2 rows terug, en wel waar id=1 en id=2. Dus dat lijkt te kloppen.
maar waarom MAX dan niet de row met id=4 teruggeeft snap ik niet.....

[ Voor 18% gewijzigd door marty op 20-12-2002 23:56 ]

[Verwijderd]

Probeer te begrijpen wat GROUP BY, MAX en HAVING doen.

GROUP BY: een set records wordt samengenomen op basis van een waarde in een kolom tot 1 record. Voor het nieuwe, 'virtuele' record dat ontstaat is het duidelijk welke waarde in die kolom komt, aangezien die voor alle records gelijk is.

Voor de andere records is het echter niet duidelijk. Daarom kan je de GROUP BY functies gebruiken om een zinvolle waarde te halen uit alle kolomwaarden binnen de groep. Denk dan aan COUNT en MAX.

Om dit te verduidelijken een paar voorbeelden:

code:

Tabel PRODUCT:
ID Name    Type               Price
1  Pen        Schrijfwaren    2
2  Potlood  Schrijfwaren    1
3  Stoel      Meubulair        5
4  Klok        Accesoires      3

Query: (toon van iedere product type hoeveel producten er zijn)
SELECT Type, Count(*) AS NumProds
FROM Product
GROUP BY Type

Resultaat:
Type              NumProds
Accesoires       1
Meubilair         1
Schrijfwaren    2

In de query zou het opnemen van de kolom ID leiden tot een fout. Er valt namelijk niets te zeggen over welke van de 2 ID's in de group Schrijfwaren getoond zou moeten worden. Ik denk dat de meeste mensen dat met dit voorbeeld ook wel kunnen inzien. Het wordt echter in hun perceptie anders met het volgende voorbeeld.

code:

Query: (toon van de producten per product type de hoogste prijs)
SELECT Type, Max(Price) AS MaxPrice
FROM Product
GROUP BY Type

Resultaat:
Type              MaxPrice
Accesoires       3
Meubilair         5
Schrijfwaren    2

In dit geval zou men verwchten dat, als de kolom ID wordt toegevoegd, de ID van het record met de hoogste prijs terug te krijgen. Dit is niet zo, en leidt net zo goed tot een foutmelding. De MAX() functie geeft alleen de maximale waarde binnen de kolom binnen de betreffende groep terug, maar heeft geen effect op de andere kolommen (en dat geldt voor alle GROUP BY functies).

Er is binnen sommige databases wel een alternatieve truuk mogelijk. Er is dan een GROUP BY functie Fisrt() (of iets anders genoemd), waarbij je de waarde van het eerste record in die kolom binnen die groep kunt ophalen. Als je de records binnen de groep dus sorteert met een ORDER BY kan je wel aan de juiste waarde komen, in dit geval met de volgende query:

code:

Query: (toon van de producten van iedere product type welke de hoogste prijs heeft)
SELECT Type, First(ID), Max(Price) AS MaxPrice
FROM Product
GROUP BY Type
ORDER BY Price

Resultaat:
Type             ID  MaxPrice
Accesoires     4     3
Meubilair        3    5
Schrijfwaren  1     2

Waar je met WHERE bepaalde records kunt uitsluiten van de resultaatset, kun je met HAVING bepalen welke groepen uitgesloten worden. Op basis van dezelfde gegevens nogmaals wat voorbeelden om de verschillen duidelijk te maken.

code:

Query: (toon het aantal producten met een prijs hoger dan 1 per product type)
SELECT Type, Count(*) AS NumProds
FROM Product
WHERE Price > 1
GROUP BY Type

Resultaat:
Type              NumProds
Accesoires       1
Meubilair         1
Schrijfwaren    1

Query: (toon het aantal producten met een prijs hoger dan 1 per product type voor producttypes met meer dan 1 product)
SELECT Type, Count(*) AS NumProds
FROM Product
GROUP BY Type
HAVING Count(*) > 1

Resultaat:
Type              NumProds
Schrijfwaren    2

Nu naar jouw probleem. Ik vermoed dat je van ALLE users de laatste aktie wil zien, dus in dat geval is een HAVING niet aan de orde. Ook wil je geen records uitsluiten binnen de groep, dus een WHERE eveneens niet zinvol.

Wat ik zie uit jouw resultaten is dat MySQL, in het geval dat je niet expliciet een GROUP BY functie gebruikt, hij automatisch First() gebruikt om een zinvol resultaat terug te geven, ipv een foutmelding te genereren. Als dat klopt, zou de volgende query een goed resultaat moeten geven.

code:

SELECT id, veld1, veld2 MAX(tijd) AS maxtijd
GROUP BY user_id
ORDER BY tijd

Ik hoop dat je GROUP BY in al zijn rariteiten wat beter begrijpt, en dat deze query het nog doet ook.

Succes

[KillR-B]

Ik ben het nu al een half uur aan het proberen, maar ik kom er ook niet uit. GROUP BY geeft standaard het eerste record binnen de group terug. Ik heb het geprobeerd met 'GROUP BY user_id DESC' en HAVING, maar ook dat mocht niet baten .

KillR-B is daarom ook zeer benieuwd wat het kan zijn...

[ACM]

Wat MrX eigenlijk verteld is dat wat de topicstarter wil niet kan met group by/having

Met een andere database dan mysql kan het zoiets worden:
select * from tabel t1 where tijd = (select max(tijd) from tabel t2 where t1.userid = t2.userid)

Zoals je in dit resultaatset van postgresql kan zien:

code:

acm=# select * from bla;
 id | user_id | veld1 | veld2 | veld3 | tijd
----+---------+-------+-------+-------+------
  1 |       3 | bla   | dibla |       |   10
  2 |       3 | tra   | la    | la    |   12
  3 |       3 | ti    | di    | di    |   14
  4 |       1 | ta    | da    | da    |   13
  5 |       1 | tral  | laa   | li    |   14
(5 rows)

acm=# select * from bla b1 where tijd = (select max(tijd) from bla b2 where b1.user_id = b2.user_id);
 id | user_id | veld1 | veld2 | veld3 | tijd
----+---------+-------+-------+-------+------
  3 |       3 | ti    | di    | di    |   14
  5 |       1 | tral  | laa   | li    |   14
(2 rows)

Mysql ondersteund echter (voorlopig, dat zal nog wel meer dan een jaar duren) geen subqueries dus bovenstaande query is daar niet in mogelijk. Volgens mij is dit niet of nauwelijks om te schrijven naar een gewone query. Ik zou het in ieder geval niet kunnen, maar wellicht is er in de mysql-manual over het omschrijven van subqueries naar gewone queries nog iets te vinden.

En anders moet je zelf de query omschrijven naar twee stuks.
Bijv eerst deze:
select user_id, max(tijd) as maxtijd from tabel group by user_id;
en dmv een while door het resultaat ervan gaan en steeds:
select * from tabel where user_id = row[user_id] and tijd = row[maxtijd]


Btw, deze mocht ook van postgres: select * from bla where (user_id, tijd) IN (select user_id, max(tijd) from bla group by user_id);

[ Voor 17% gewijzigd door ACM op 21-12-2002 14:22 ]

[Apollo_Futurae]

ik geloof dat dit werkt:

code:

SELECT t1.*
FROM test AS t1
     INNER JOIN
     test AS t2
       ON (t1.user_id = t2.user_id)
GROUP BY t2.user_id, t1.id
HAVING t1.tijd = MAX(t2.tijd)

mooi is het bepaald niet; mysql moet je eigenlijk niet vermoeien met dit soort 'moeilijke' queries