[MySQL] ORDER GROUP geeft verkeerde volgorde

[florisje]

ik heb er weer eentje

Hier de voorbeelddata:

code:

CREATE TABLE IF NOT EXISTS `person` (
  `pe_id` int(11) NOT NULL auto_increment,
  `pe_name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`pe_id`)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS `visit` (
  `vi_id` int(11) NOT NULL auto_increment,
  `vi_pe_id` int(11) NOT NULL default '0',
  `vi_date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`vi_id`),
  KEY `vi_pe_id` (`vi_pe_id`)
) TYPE=MyISAM;

TRUNCATE person;
TRUNCATE visit;

INSERT INTO person (pe_id, pe_name) VALUES
(1,'person 1'),(2,'person 2'),(3,'person 3');

INSERT INTO visit (vi_id,vi_pe_id,vi_date) VALUES
(1,1,'2004-04-01'),(2,1,'2004-05-01'),(3,1,'2004-06-01'),
(4,2,'2004-04-02'),(5,2,'2004-05-02'),(6,2,'2004-06-02'),
(7,3,'2004-04-03'),(8,3,'2004-05-03'),(9,3,'2004-06-03');

Ik wil hebben: de laatste bezoekdata van de 3 personen.
Als ik nu de volgende query draai ...

code:

SELECT *, MAX(vi_date)
FROM person
JOIN visit ON pe_id=vi_pe_id
GROUP BY pe_id
ORDER BY vi_date DESC;

... krijg ik niet het laatste bezoek van de personen.
(Zie de MAX(vi_date) die niet gelijk is aan vi_date.)
Dat komt doordat MySQL gewoon het eerste record
geeft en daarna pas de ORDER doet.

Het omdraaien van de tabelvolgorde werkt niet en
is ook geen oplossing, omdat we eigenlijk nog veel
meer joins hebben, maar die zijn even weggelaten
om het probleem duidelijk te maken...

Iemand een handig manier?

[ Voor 9% gewijzigd door florisje op 06-07-2004 09:46 ]

[whoami]

Je GROUP BY is verkeerd.
Kijk maar eens in een willekeurige tutorial: je moet grouperen op alle velden die in je select-list staan, en geen resultaat van een aggregate-functie zijn.

Waarom MySQL het toelaat om die group by zo te schrijven zoals jij doet, is me een raadsel.

[florisje]

Dat is nou een van de voordelen van mysql tov mssql

MySQL extends the use of GROUP BY so that you can use columns or calculations in the SELECT list that don't appear in the GROUP BY clause. This stands for any possible value for this group. You can use this to get better performance by avoiding sorting and grouping on unnecessary items.

http://dev.mysql.com/doc/...OUP-BY-hidden-fields.html

[whoami]

Het is gewoon geen voordeel, want het is fout.

Het wordt afaik niet ondersteund door de standaard.

[florisje]

'de standaard'? een extensie op de standaard is inderdaad niet standaard, maar ik gebruik de standaard ook niet...

als ik groepeer op alle velden (en dan niet de MAX() selecteer) krijg ik meerdere resultaten per persoon en dat wil ik niet...

iemand die wel een oplossing weet? (whoami: misschien kun je de query geven waarmee het onder mssql wel werkt?)

[whoami]

code:

select person.naam, max(date) as blaat
from person
group by person.naam
order by blaat desc

[bigbeng]

En doordat MySQL met de standaard breekt (het is geen extensie maar een verandering) krijg je het gedrag zoals je nu beschrijft.

Je zult met subselects moeten werken om de query te krijgen zoals je die wilt. En dat kan pas vanaf MySQL4 geloof ik.

[ACM]

De Aggregate/group by heeft geen relatie met individuele records, maar met de groep van records die je opgeeft met group by. Dus je krijgt wel de MAX van vi_date voor die persoon, maar niet een die perse bij de rest van het getoonde individuele record hoort.

Dat is het grootste nadeel van group by imho, een oplossing is er niet in MySQL omdat de oplossingen (die ik ken) allemaal subqueries of extra extenties gebruiken.
In MySQL ben je in dit geval ofwel gedwongen om eerst los de max-data per persoon op te halen (ongeveer de query van whoami) en dan die te gebruiken in je vervolgquery.

't Snelst is zoiets:

SQL:

CREATE TEMPORARY TABLE max_dates 
      AS SELECT pe_id, MAX(pe_date) as pe_date FROM person GROUP BY pe_id;
SELECT p.* FROM 
     person p JOIN max_dates m ON p.pe_id = m.pe_id AND p.pe_date = m.pe_date;

bigbeng schreef op 06 juli 2004 @ 10:05:
En doordat MySQL met de standaard breekt (het is geen extensie maar een verandering) krijg je het gedrag zoals je nu beschrijft.

Niet echt, 't is gewoon inherent aan de manier waarop GROUP werkt, ook in de standaard.

[ Voor 18% gewijzigd door ACM op 06-07-2004 10:13 ]

[florisje]

ACM, bedankt. maar jouw temporary table heeft al de verkeerde ids.

dat zijn gewoon de ids van de eerste records, niet die van MAX() records...

tis echt een lastig probleem.

subquerys zou het inderdaad kunnen oplossen, maar er is mij altijd verteld dat alles wat met subquerys kan ook zonder mogelijk is.

[florisje]

crap, ik zit net nog eens naar m'n eigen link te kijken, en daarin staat ook weer een link naar:

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

daarin staat gewoon de oplossing voor mijn probleem.

[bigbeng]

florisje schreef op 06 juli 2004 @ 10:23:
ACM, bedankt. maar jouw temporary table heeft al de verkeerde ids.

dat zijn gewoon de ids van de eerste records, niet die van MAX() records...

tis echt een lastig probleem.

subquerys zou het inderdaad kunnen oplossen, maar er is mij altijd verteld dat alles wat met subquerys kan ook zonder mogelijk is.

Dat klopt, omdat je namelijk temptables kunt gebruiken als alternatief (eigenlijk de oorspronkelijke oplossing voor dit soort problemen). En mij is dus weer geleerd dat je indien mogelijk temptables moet proberen te vermijden en dat kan dus weer met subselects. Leuk he, dit soort tegenstrijdigheden.

ACM schreef op 06 juli 2004 @ 10:12:
...
Niet echt, 't is gewoon inherent aan de manier waarop GROUP werkt, ook in de standaard.

Uh, de standaard werkt niet, het is een beschrijving. En daarin wordt volgens mij heel duidelijk gesteld dat bij het gebruik van aggregate functies alle velden die niet geaggregeerd worden moeten worden genoemd in de group by. Maar goed, ik heb de standaardocumenten al een tijdje niet meer ingezien en met mijn geheugen kan ik er best naast zitten.

[ACM]

florisje schreef op 06 juli 2004 @ 10:27:
crap, ik zit net nog eens naar m'n eigen link te kijken, en daarin staat ook weer een link naar:

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

daarin staat gewoon de oplossing voor mijn probleem.

En dat is vrijwel exact de query die ik hierboven geef, dat ie niet klopt kan best. 't Ging mij vooral om de structuur.

bigbeng schreef op 06 juli 2004 @ 10:28:
Uh, de standaard werkt niet, het is een beschrijving. En daarin wordt volgens mij heel duidelijk gesteld dat bij het gebruik van aggregate functies alle velden die niet geaggregeerd worden moeten worden genoemd in de group by. Maar goed, ik heb de standaardocumenten al een tijdje niet meer ingezien en met mijn geheugen kan ik er best naast zitten.

Dat je die velden allemaal ofwel moet aggregeren ofwel moet groupen weet ik, maar dat is niet mijn punt en ook niet het punt dat voor het foute resultaat zorgt. Weliswaar staat de manier van MySQL's groupering dit toe, maar in de standaard is bij mijn weten ook geen enkele relatie tussen de aggregated-waarde (die max bijv) en het record dat erbij getoond wordt, alleen maar met de waarde en de groep waar ie uitkomt.

't Is wel zo dat als je je aan de standaard houdt, je dit soort geintjes minder makkelijk kan uithalen, omdat je gedwongen bent een andere oplossing te gebruiken (je kan nauwelijks select *, MAX(...) doen met de standaard-aanpak zonder een hoop geklooi).

[florisje]

En dat is vrijwel exact de query die ik hierboven geef, dat ie niet klopt kan best. 't Ging mij vooral om de structuur.

inderdaad, alsnog respect!

ik ben het nu aan het proberen met een temporary table...

bedankt allemaal voor de tips