[SQL] GROUP BY: alleen records met nieuwste datum

[Wolf3D]

Na een langdurige zoektocht heb ik erg veel topics over mijn onderwerp gevonden, maar .. geen oplossing. Conclussie trekkende uit het topic [rml][ SQL] Group by + MIN werkt niet ?[/rml] is dat het niet kan wat ik wil .. maar .. ik kan het me eigenlijk niet voorstellen, dus toch nog maar ff het probleem met jullie delen:

Ik heb een tabel met daarin facturen van klanten. Deze facturen zijn gekoppeld aan een advertentie, er kunnen meerdere facturen bij een advertentie horen.

De tabel ziet er ongeveer zo uit:

code:

factuurID     advertentieID      datum
1             100                01-01-05
2             106                11-12-04
3             120                03-06-05
4             100                01-06-05
5             100                14-10-05

Nou wil ik alle records selecteren waarbij indien hetzelfde advertentieID meerdere keren voorkomt het record wordt gepakt met de nieuwste datum.

Ik wil dus terug:

code:

factuurID     advertentieID      datum
2             106                11-12-04
3             120                03-06-05
5             100                14-10-05

Dit dacht ik eenvoudig voor elkaar te krijgen .. maar na heel wat pogingen met DISTINCT, GROUP BY, MAX, HAVING .. enz .. geef ik het nu na een halve dag prutsen op.

De laatste poging is:

code:

SELECT factuurID, advertentieID, MAX(datum)
FROM tabel
GROUP BY advertentieID

Maar hierbij koppelt hij gegegens van verschillende records aan elkaar. Waarschijnlijk is de query ook fout, aangezien er niets gebeurd met factuurID. Maar als ik deze in de GROUP BY clausule opneem krijg ik weer gewoon herhaalde rijen van advertentieID te zien en niet alleen unieke.

Om een lang verhaal niet nog langer te maken .. ik kom er niet meer uit .. en loop helemaal vast .. en zie door alle aggregaties het bos niet meer.

Toevoeging: Het gaat hier om MySQL (Versie 4.0.25)

[ Voor 4% gewijzigd door Wolf3D op 03-11-2005 13:03 ]

[justmental]

Dit los je op met een subquery, niet met een group by.
Dus in de trant van where datum = (select max(datum...

[EfBe]

wilde gok:
select * from tabel T1 where T1.factuurID=(select TOP 1 factuurID from table T2, where T1.advertentieID = T2.advertentieID order by datum DESC)

[Wolf3D]

Hmmmm, MySQL ......
Geen mogelijkheid voor subqueries dus ..

[EfBe]

Wolf3D schreef op donderdag 03 november 2005 @ 12:42:
Hmmmm, MySQL ......
Geen mogelijkheid voor subqueries dus ..

Het kan volgens mij ook met een groupby en een having clause. En welke oude butt versie gebruik je dan, want mysql 4.x ondersteunt toch wel subqueries of ben ik nou gek?

[ Voor 19% gewijzigd door EfBe op 03-11-2005 12:45 ]

[ahendriks]

Wat bijvoorbeeld kan is:

select tab.*
from tabel tab
where 1 < (select count(9) from tabel tab2 where tab2.advertentieid = tab.advertentieid)
and datum = (select max(tab2.datum) from tabel tab2 where tab2.advertentieid = tab.advertentieid)

Er zijn wel meer mogelijkheden, maar deze heb ik nu even snel uitgewerkt...

suc6 ermee

edit:
Wat nou MySQL, dit zie ik niet in de hoofdpost terug komen. Mijn voorbeeld werkt met oracle, als mysql ook subqueries aankan werkt het daar ook bij

[ Voor 20% gewijzigd door ahendriks op 03-11-2005 12:49 ]

[Wolf3D]

Ik .. of eigenlijk mijn hostingprovider ..gebruikt versie 4.0.25. Volgens mij werken subqueries pas vanaf 4.1 of zelfs 5.0?

Wel ga ik binnenkort verhuizen naar een andere hostingprovider die 4.1.13 draait, maar daar heb ik nu weinig aan ...

Dus, zijn er oplossingen zonder subqueries? Of maak ik het mezelf dan onnodig moeilijk en kan ik beter wachten tot na de verhuizing?

[justmental]

Hier staat een alternatief zonder subquery's voor mysql:
http://dev.mysql.com/doc/...mum-column-group-row.html

[Annie]

Wolf3D schreef op donderdag 03 november 2005 @ 12:55:
Dus, zijn er oplossingen zonder subqueries? Of maak ik het mezelf dan onnodig moeilijk en kan ik beter wachten tot na de verhuizing?

Ja, twee queries. Eerst de minimale datums en advertenties ophalen en dan een query opstellen voor de bijbehorende facturen.

Over hoeveel records praten we hier eigenlijk? Bij weinig resultaten kan je de facturen in een while ophalen.

code:

SELECT advertentieID, MIN(datum) FROM tabel

Voor elke record
  SELECT factuurID FROM tabel WHERE advertentieID = X AND datum = Y

Als dit teveel requests op de database betekent, dan zou je ook een sql string kunnen opbouwen

code:

SELECT advertentieID, MIN(datum) FROM tabel

sql = SELECT factuurID FROM tabel WHERE 1=1

Voor elke record
  sql = sql + " AND (advertentieID = X AND datum = Y)"

Voer sql uit

Andere optie: stop de tussenresultaten in een temptable en join daarop.

Ranzigheid ten top

/edit: damn you, justmental
oeh, die max-concat truuk is vies, die moet ik onthouden

[ Voor 7% gewijzigd door Annie op 03-11-2005 13:21 ]

[Wolf3D]

justmental schreef op donderdag 03 november 2005 @ 13:14:
Hier staat een alternatief zonder subquery's voor mysql:
http://dev.mysql.com/doc/...mum-column-group-row.html

Hmmm, ziet er behoorlijk onefficient uit idd .. hoewel ik het niet heb kunnen testen aangezien ik niets snap van dat CONCAT() gebeuren. Ook een zoektocht op MySQL.com levert geen helder antwoord op.

Annie schreef op donderdag 03 november 2005 @ 13:18:
[...]

Ja, twee queries. Eerst de minimale datums en advertenties ophalen en dan een query opstellen voor de bijbehorende facturen.

Over hoeveel records praten we hier eigenlijk? Bij weinig resultaten kan je de facturen in een while ophalen.

Twee queries lijkt me dan idd de beste oplossing. De hoeveelheid records per gebruiker is als het goed is niet zo hoog. Gemiddeld minder dan 10 verwacht ik. Het totaal aantal records kan wel oplopen.

[Wolf3D]

Na een tijdje klooien, toch maar weer ff hulp halen ...
Ik kom er niet uit .. ook niet met twee queries.

Het voorbeeld dat Annie gaf:

code:

SELECT advertentieID, MIN(datum) FROM tabel

Voor elke record
  SELECT factuurID FROM tabel WHERE advertentieID = X AND datum = Y

werkt niet. Of ik snap het (waarschijnlijk) niet!? Aangezien ik GROUP BY moet gebruiken zodra ik MIN() of MAX() gebruik. (Ik neem aan dat ik MAX() ipv MIN() moet gebruiken..) Maar zodra ik GROUP BY erbij haal worden weer de gegevens uit verschillende records door elkaar gegooid. Daardoor kom ik niet aan het juiste factuurID welke ik volgens mij moet hebben om de overige gegevens uit die record te vinden.

Ik ben hem helemaal kwijt. Denk dat ik toe ben aan weekend .. of een nieuwe hostingprovider welke subselects ondersteunt.

[bigbeng]

Waarop group by je dan?

[Wolf3D]

Dat moet van meneer MySQL

code:

MySQL said:  

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

[bigbeng]

Waarop vroeg ik
Dus op welke velden groepeer je?

[Wolf3D]

Ah! Waarop! Lezen is ook een vak!

Ik group by op advertentieID.

Inmiddels een week later .. en het probleem is nog steeds niet opgelost. De verhuizing is aangevraagd dus hopelijk kan ik nu snel over zodat ik verder met dit probleem kan.

[Janoz]

Tja, in je eerste query zie ik je wel een min gebruiken, maar geen group by.

[Reinier]

Janoz schreef op woensdag 09 november 2005 @ 14:07:
Tja, in je eerste query zie ik je wel een min gebruiken, maar geen group by.

Inderdaad, maar vervolgens zegt hij:

Maar als ik deze in de GROUP BY clausule opneem krijg ik weer gewoon herhaalde rijen van advertentieID te zien en niet alleen unieke.

Het leek mij dé oplossing, maar blijkbaar wil het niet.

[Wolf3D]

Vandaag ben ik er weer eens ff goed ingedoken, en heb het nu werkend gekregen met jullie hulp!
Ik denk dat ik het vorige week ff helemaal kwijt was (weet zelfs zeker) .. Ik had zoveel zitten proberen dat ik de draad ben kwijtgeraakt ..

Heb nu iets dat werkt, maar ik vertrouw het eigenlijk nog voor geen meter... Moet dus nog even goed testen of het ECHT werkt.

code:

SELECT advertentieID, MAX(datum) AS datum
FROM tabel 
WHERE gebruikersID = '$ingelogdegebruiker'
GROUP BY advertentieID

De $advertentieID en $datum gebruik ik vervolgens in de volgende query:

code:

SELECT advertentieID, anderegegevens
FROM tabel
WHERE advertentieID = '$advertentieID'
AND datum = '$datum'
GROUP BY advertentieID

Dit lijkt te werken...

[whoami]

Waarom gebruik je group by op die tweede manier (MySQL is het enige DBMS dat ik ken die dit toelaat).
Op wat groepeer je ? Wat moet er getoond worden voor 'anderegegevens' ?

[curry684]

Je snapt geen bal van group by, lees dit eens.

[Wolf3D]

Hmmm, jullie hebben gelijk, wat betreft dat die 2e GROUP BY nutteloos is, aangezien ik natuurlijk al bij de eerste query al de dubbelen er tussenuit filter.

Het geen bal snappen van het group by-gebeuren .. klopt niet, vind ik! Hoewel ik wel af en toe ff het overzicht kwijt ben ...

[ Voor 3% gewijzigd door Wolf3D op 09-11-2005 15:32 ]

[jantje_20]

Kan onderstaande oplossing (gebouwd en getest op SQL server) niet testen op MySQL, maar het lijkt me dat het daar ook wel op werkt.

code:

SELECT t2.FactuurId, t1.advertentieID, MAX(t1.datum)
FROM tabel t1 INNER JOIN
 tabel t2 ON t1.advertentieID = t2.advertentieID
GROUP BY t2.FactuurId, t1.advertentieID, t2.Datum
HAVING MAX(t1.datum) = t2.Datum

[Janoz]

Wolf3D schreef op woensdag 09 november 2005 @ 15:31:
Hmmm, jullie hebben gelijk, wat betreft dat die 2e GROUP BY nutteloos is, aangezien ik natuurlijk al bij de eerste query al de dubbelen er tussenuit filter.

Het geen bal snappen van het group by-gebeuren .. klopt niet, vind ik! Hoewel ik wel af en toe ff het overzicht kwijt ben ...

Het eerste bold statement geeft imho duidelijk aan dat je van group by geen bal snapt . Een group by is niet een filter tegen dubbelen, maar een functie die geagregeerde data groepeerd.

[jantje_20]

Janoz schreef op woensdag 09 november 2005 @ 15:53:
[...]


Het eerste bold statement geeft imho duidelijk aan dat je van group by geen bal snapt . Een group by is niet een filter tegen dubbelen, maar een functie die geagregeerde data groepeerd.

laat dat 'geagregeerde' maar weg, dan klopt het. Overigens is je uitleg dan niet meer dan een rechtstreekse vertaling uit het engels

[whoami]

Een group by heeft enkel zin als je ge-aggregeerde data selecteert, dus, is het wel het groeperen van geagregeerde data.

[Wolf3D]

Janoz schreef op woensdag 09 november 2005 @ 15:53:
[...]


Het eerste bold statement geeft imho duidelijk aan dat je van group by geen bal snapt . Een group by is niet een filter tegen dubbelen, maar een functie die geagregeerde data groepeerd.

Het was idd een beetje kort door de bocht .. of .. verkeerd omschreven door mij ... maar uiteindelijk resulteert het 'grouperen' in het samenvoegen van de records die geen identieke waardes bevatten, wat neerkomt op het alleen laten zien van de records welke identiek zijn (dubbele eruit gooien) (wat via group by moet worden opgegeven). Nahgoed, jullie kunnen dit allemaal beter beschrijven dan ik...zoals inmiddels al gedaan is zie ik. (Ik type te langzaam....)

In ieder geval doe ik dat volgens mij wel goed hierboven .. op de 2e GROUP BY na.

[jantje_20]

whoami schreef op woensdag 09 november 2005 @ 16:19:
Een group by heeft enkel zin als je ge-aggregeerde data selecteert, dus, is het wel het groeperen van geagregeerde data.

Spreek je jezelf nu niet tegen? Eerst zeg je dat groeperen geen zin heeft op ge-aggregeerde data, vervolgens stel je: 'dus, is het wel het groeperen van geagregeerde data.'

[whoami]

Je moet echt beter lezen.
Ik zeg:

een group by heeft enkel zin als je ge-aggregeerde data selecteert

[ Voor 3% gewijzigd door whoami op 09-11-2005 16:46 ]

[curry684]

Wolf3D schreef op woensdag 09 november 2005 @ 16:24:
[...]


Het was idd een beetje kort door de bocht .. of .. verkeerd omschreven door mij ... maar uiteindelijk resulteert het 'grouperen' in het samenvoegen van de records die geen identieke waardes bevatten, wat neerkomt op het alleen laten zien van de records welke identiek zijn (dubbele eruit gooien) (wat via group by moet worden opgegeven). Nahgoed, jullie kunnen dit allemaal beter beschrijven dan ik...zoals inmiddels al gedaan is zie ik. (Ik type te langzaam....)

D'r klopt idd geen bal van GROUP BY dient om identieke waardes uit de opgegeven kolommen samen te nemen, waarna je vervolgens voor de overgebleven kolommen een aggregate-functie nodig hebt om het DBMS duidelijk te maken welke waarde je wil zien bij de samengenomen rows. En als je dat goed snapt weet je direct waarom je vraag uit de TS niet kan met group by

Dus nogmaals:

curry684 schreef op woensdag 09 november 2005 @ 15:17:
Je snapt geen bal van group by, lees dit eens.

Tis echt niet onvriendelijk bedoeld, maar je snapt het gewoon niet en ik geef je een stukje aan dat ik niet voor niets aan de Frequently Asked Questions heb toegevoegd ooit: dit soort vragen komt wekelijks langs

[Wolf3D]

Het aangegeven stukje had ik meteen (nog eens) goed bestudeerd, toen je de link hierboven gaf. Ik had het stukje ook al eerder doorgelezen, nog voordat ik ben gestart met dit topic.

Moet toegeven dat het een lastige blijft. Met die voorbeelden uit jouw FAQ is het erg duidelijk en lijkt het vrij eenvoudig, maar als je het dan moet toepassen op je eigen situatie, valt het altijd erg tegen. Vind ik iig.

Ik snap dat als je een tabel hebt met dezelfde waardes, zoals de tabel met die getalletjes in je FAQ dat je dan aan de database moet duidelijk maken waarop hij moet groeperen, aangezien de database niet weet welke waarde je wil. Maar, als ik nou een tabel heb waarvan twee rijen niet herhalend mogen zijn, en daarnaast ook nog een hele rits andere gegevens erbij gekoppeld moet worden, zoals in mijn situatie. Dan kan ik toch niet zonder GROUP BY? Met DISTINCT kom ik er iig niet ...

Je zegt dat wat ik wil niet met group by kan!? Maar neem aan dat dat niet slaat op het stukje dat ik als werkende code heb aangegeven? Toch? Dat lijkt iig te werken hier...

[Janoz]

De denk fout die veel mensen maken is dat de berekende waarde een record zou zijn. Dat is het niet. En neemt een rij met waarden en gooit deze door een functie heen die een resultaat oplevert. Dit is niet een van de voorgaande waarden en is dus ook niet gekoppeld aan een record. De problemen treden vaak op doordat het resultaat van MIN en MAX een waarde is uit die lijst getallen. Veel mensen worden hierdoor op het verkeerde been gezet. Maar bedenk nu eens zelf. Bij welk record zou een gemiddelde horen? Welk record zou er terug moeten worden gegeven wanneer er 3 zijn met dezelfde max waarde?

Het enige dat een group by doet is de lijst met invoer in verschillende groepen verdelen en vervolgens de functie per groep loslaten.

Over het stukje:
De group by in de 2e query is onzin. Je gebruikt niet eens een aggregerende functie.

[ Voor 7% gewijzigd door Janoz op 09-11-2005 19:40 ]

[Wolf3D]

Je hebt gelijk, die tweede GROUP BY was behoorlijk nutteloos.

Verder ben ik er inmiddels ook van overtuigd dat GROUP BY niet de juiste/beste oplossing is.
De geposte query die ik had werkt, ware het niet dat er weer andere problemen ontstaan, bijv. met sorteren. Aangezien hij record voor record afgaat en van die record de juiste gegevens erbij haalt in een 2e query kan er niet goed gesorteerd worden. Dat is bij het weergeven van overzichten niet handig.
Werken via subselect is dus denk ik de enige mogelijkheid om dit netjes, eenvoudig en probleemloos aan te pakken...

EfBe schreef op donderdag 03 november 2005 @ 12:42:
wilde gok:
select * from tabel T1 where T1.factuurID=(select TOP 1 factuurID from table T2, where T1.advertentieID = T2.advertentieID order by datum DESC)

Deze query die ergens helemaal aan het begin van dit topic is gegeven heb ik losgelaten (enig aangepast : TOP 1 verwijderd en LIMIT 0, 1 aan einde toegevoegd) op een andere database waar WEL 4.1+ geinstalleerd is, en het werkt als een trein, gesorteerd en al!
Dat wordt dus nog ff wachten (pakket is maandag aangevraagd) totdat ik bij de nieuwe hostingprovider aan de slag kan .......

Iedereen bedankt voor jullie tijd en het duidelijk uitleggen van de GROUP BY-functie!!