[MySQL] Group by: bepaalde rijen 'voorrang' geven

[MisterData]

Stel ik heb de volgende database:

code:

tabel saldo_gegevens

rek. nr.    saldo   datum
1           100 100
1           200 101
1           150 102
2           512 100
2           400 102

Nu wil ik van alle rekeningen de meest recente waarde ophalen. Dus eigenlijk wil ik van zowel rekening 1 en 2 het saldo weten op datum=102. Maar stel dat de laatste regel in het voorbeeld niet bestaat, dan wil ik het saldo van datum=100 weten van rekening 2. Hoe doe ik dat met MySQL? Ik kwam zelf op zoiets:

SELECT saldo FROM saldo_gegevens GROUP BY rek_nr ORDER BY rek_nr;

Voer ik deze (overigens illegale!) constructie uit met MySQL dan krijg je voor saldo in principe random resultaten: immers je geeft niet aan welke 'saldo' je wil hebben van de geaggregeerde saldo's. Maar MAX(saldo) kan ook niet, want ik wil niet het hoogste saldo, ik wil het meest recente saldo. Ik ben dus op zoek naar iets dat het volgende doet:

SELECT HET_MEEST_RECENTE(saldo) FROM saldo_gegevens GROUP BY rek_nr ORDER BY rek_nr;

Iemand een ideetje?

[Verwijderd]

Binnen Access weet ik dat je een aggregate function first() hebt om de bovenste regel binnen een groep te pakken. Als je dan groepeert op rekeningnummer en sorteert op datum heb je wat je wil hebben. Of dat met MySQL kan weet ik niet, da's even zoeken in de manual.

[MisterData]

Volgens mij kent MySQL die functie niet, althans ik kan hem zo snel niet vinden. Ik zat zelf ook al te denken over een soort voorgebakken tabel die ik genereer met een script en zo de laatste waardes opslaat, maar het probleem is dat in de applicatie waar ik dit probleem tegenkom (en die verder niks met rekeningen of saldo's maar met benchmarks en waardes per jaar te maken heeft) ik niet zomaar zo'n tussentabel zou kunnen maken die geldt voor iedere gebruiker, omdat iedereen andere rechten heeft op bepaalde gegevens. Dan zou ik per gebruiker of desnoods per groep gebruikers die dezelfde rechten heeft een eigen tussentabel moeten maken, ik vrees dat dat te lastig wordt...

[jochemd]

Probeer de handleiding eens.

[H!GHGuY]

SQL:

select rek_nr as R from saldo_gegevens where datum = (Select MAX(datum) from saldo_gegevens where rek_nr = R)

[DukeBox]

SELECT rek_nr, saldo FROM saldo_gegevens WHERE date='vandaag' GROUP BY rek_nr niet een oplossing ?

[MisterData]

DukeBox schreef op maandag 22 augustus 2005 @ 17:48:
SELECT rek_nr, saldo FROM saldo_gegevens WHERE date='vandaag' GROUP BY rek_nr niet een oplossing ?

Nee, dan krijg ik alleen de gegevens van vandaag, terwijl het helemaal niet gegarandeerd is dat er iedere dag saldogegevens worden geschreven. Ik zal even uitleggen waarom dat precies zo is. In de applicatie die ik maak krijgen we gegevens van allerlei bedrijven, meestal elk jaar. Het probleem is dat sommige bedrijven het vorige jaar bijvoorbeeld wel, maar dit jaar niets hebben ingeleverd. Ik ben nu een functie aan het maken in onze benchmark-applicatie die oude gegevens van dát bedrijf (met inflatiecorrectie etc) gebruikt indien er geen nieuwe gegevens in de database staan. Dus als ik bedrijf X en Y vergelijk, en X heeft gegevens uit 2002 en Y gegevens uit 2002 en 2003, dan wil ik van X 2002 en van Y 2003 terugkrijgen. Aangezien alle oudere gegevens ook in die tabel staan is deze query dus nodig

[ Voor 10% gewijzigd door MisterData op 22-08-2005 17:53 ]

[ShadowLord]

OK, met MySQL is dit erg makkelijk omdat MySQL 'GROUP BY' ondersteund met verborgen velden..

Probeer deze query eens:

SQL:

SELECT rekening, saldo, MAX(datum) AS maxdatum FROM saldo_gegevens WHERE datum = maxdatum

Edit: OK, deze klopt niet, maar het kan dus met een GROUP BY - ik graaf even uit m'n geheugen op hoe het ook al weer zat.

Edit2: OK, toch geen group by (zat te denken aan een andere probleem) Zal toch met een sub-query of 2 losse queries moeten. Lame SQL ook soms... Aan de andere kant, wees bly dat je met MySQL werkt. MSSQL (waaraan ik op het moment be overgeleverd) is echt niet relaxed als het gaat om dingen als GROUP BY.

[ Voor 56% gewijzigd door ShadowLord op 22-08-2005 18:07 ]

[MisterData]

ShadowLord schreef op maandag 22 augustus 2005 @ 17:52:
OK, met MySQL is dit erg makkelijk omdat MySQL 'GROUP BY' ondersteund met verborgen velden..

Probeer deze query eens:

SQL:

SELECT rekening, saldo, MAX(datum) AS maxdatum FROM saldo_gegevens WHERE datum = maxdatum

Zo op het eerste gezicht lijkt me dat niet wat ik bedoel, want het probleem is dat als ik de volgende regels heb:

code:

1, €300, 100
1, €400, 101
1, €500, 102
2, €100, 50
2, €150, 51
2, €175, 52

MAX(datum) is dan 102, maar 2 heeft helemaal geen gegevens van datum 102...

[P_de_B]

SQL:

SELECT rekening, saldo
FROM tabel t
WHERE datum = (SELECT MAX(datum) FROM tabel WHERE rekening = t.rekening)

[MisterData]

P_de_B schreef op maandag 22 augustus 2005 @ 17:59:

SQL:

SELECT rekening, saldo
FROM tabel t
WHERE datum = (SELECT MAX(datum) FROM tabel WHERE rekening = t.rekening)

Ben benieuwd of MySQL deze subquery gaat ondersteunen Ik ga het proberen!

[MisterData]

Zo op het eerste gezicht lijkt de query niet echt performant: hoewel ik een (samengestelde/multi-column) index heb gemaakt op alle velden waarin de subquery op gezocht wordt in de WHERE-clausule daarvan haal ik met een data-tabel van ongeveer 300.000 regels van ongeveer 4 datumperiode's voor één query gemakkelijk de drie minuten, waar het ophalen van gegevens zonder het letten op de datum (gewoon een jaar selecteren dus) binnen een fractie van een seconde klaar is. Heeft iemand misschien ideeën over hoe deze query zonder een subquery kan? Want volgens mij (ik zal zo eens naar EXPLAIN ... kijken) wordt die subquery uitgevoerd voor iedere regel en dat is nogal traag, zelfs met een index, blijkbaar...

Index dus op de verkeerde kolommen, aan den lijve ondervonden wat indices kunnen doen: zonder (goede) index 181 seconde, nu 0.77

[ Voor 13% gewijzigd door MisterData op 22-08-2005 18:57 ]

[H!GHGuY]

P_de_B schreef op maandag 22 augustus 2005 @ 17:59:

SQL:

SELECT rekening, saldo
FROM tabel t
WHERE datum = (SELECT MAX(datum) FROM tabel WHERE rekening = t.rekening)

dit is nou exact (op een foutje in mijn SQL na) hetzelfde wat ik schreef...

[ripexx]

MisterData schreef op maandag 22 augustus 2005 @ 18:10:
[...]
Ben benieuwd of MySQL deze subquery gaat ondersteunen Ik ga het proberen!

Getest op MySQL 4.1.11 en dan gaat het goed , versie voor 4.1 ondersteunen geen subqueries dus..

[P_de_B]

ShadowLord schreef op maandag 22 augustus 2005 @ 17:52:
OK, met MySQL is dit erg makkelijk omdat MySQL 'GROUP BY' ondersteund met verborgen velden..
[...]
Zal toch met een sub-query of 2 losse queries moeten. Lame SQL ook soms... Aan de andere kant, wees bly dat je met MySQL werkt. MSSQL (waaraan ik op het moment be overgeleverd) is echt niet relaxed als het gaat om dingen als GROUP BY.

Sorry, maar ik denk dat je iets niet goed begrijpt aan GROUP BY. Waarom is MS SQL niet relaxed? De implementatie van MySQL is juist om te huilen, je kunt immers velden in je select opnemen die niet in een aggregate functie zitten, en ook niet in de group by.

[ripexx]

P_de_B schreef op dinsdag 23 augustus 2005 @ 08:19:
[...]



Sorry, maar ik denk dat je iets niet goed begrijpt aan GROUP BY. Waarom is MS SQL niet relaxed? De implementatie van MySQL is juist om te huilen, je kunt immers velden in je select opnemen die niet in een aggregate functie zitten, en ook niet in de group by.

Daarnaast is de GROUP BY dus niet 100% zeker welke waarde je terug krijgt. Wil je dat wel, dan zal je toch gebruik moeten maken van de querie zoals die van P_de_B

[thomaske]

Je kan het wel met 1 query oplossen, met een self-join

code:

SELECT
     s1.reknr
,   MAX(s1.datum)
,   s2.datum
,   s2.saldo
FROM 
    saldo_gegevens s1
LEFT JOIN 
    saldo_gegevens s2 
ON  
    s2.reknr = s1.reknr
GROUP BY 
    s1.reknr
,   s2.datum
,   s2.saldo
HAVING 
    s2.datum = MAX(s1.datum)