[MySQL] "GROUP BY en dubbele MAX()"

[X-Lars]

Hopelijk slaat de titel ergens op Men neme deze tabel:

code:

id   one  two three
 1  1   2   2
 2  1   2   3
 3  1   3   1
 4  2   2   3
 5  1   2   3
 6  3   2   1
 7  3   1   3
 8  2   1   3
 9  1   3   3
10  3   1   2
11  3   3   3
12  2   2   1

Nu wil ik groeperen op kolom one, binnen elke groep de hoogste two, en van deze resultaten de hoogste three. Bijvoorbeeld de rijen (id's) 3 en 9 hebben 1-3-x. Hiervan moet dus 1-3-3 (rij 9) genomen worden. Normaalgesproken zou MySQL bij een (foute) GROUP BY one gewoon die rij 3 hebben genomen (de eerste, of random, in ieder geval niet goed). En een MAX(two) nemen kan wel, maar dan raak ik de id a.h.w. weer kwijt. Het resultaat moet wel bestaan uit volledige rijen uit de originele tabel.

Het is me op zich wel gelukt:

SQL:

DROP TABLE tmp1;

CREATE TEMPORARY TABLE tmp1 (one int(4), two int(4));

INSERT INTO tmp1 SELECT one, MAX(two) FROM mytable GROUP BY one;

DROP TABLE tmp2;

CREATE TEMPORARY TABLE tmp2 (one int(4), three int(4));

INSERT INTO tmp2
SELECT m.one, MAX(m.three)
FROM mytable as m, tmp1 as t1
WHERE m.one=t1.one AND m.two=t1.two
GROUP BY one;

SELECT m.id, m.one, t1.two, t2.three
FROM mytable as m, tmp1 as t1, tmp2 as t2
WHERE m.one=t1.one AND m.one=t2.one AND m.two=t1.two AND m.three=t2.three;

In table tmp1 komt nu:

code:

one two
 1   3
 2   2
 3   3

En in table tmp2 (de hoogste waarde van three voor de combinatie one, two uit tmp1):

code:

one three
 1   3
 2   3
 3   2

Het resultaat is prachtig:

code:

id  one two three
 9   1   3   3
 4   2   2   3
11   3   3   2

Precies zoals ik het hebben wil. Maar moet het echt zo omslachtig? Ik heb wel MySQL 4.1.1 (ivm subqueries), maar o.a. d.m.v. de MySQL manual of de P&W FAQ - SQL kwam ik echter niet tot iets beters.

[pjotrk]

Het zou ook nog puur met subselects kunnen, alleen is het resultaat wel anders dan dat jij zelf aangaf (maar wel het verwachte resultaat als ik uit je verhaal begrijp?)

code:

SELECT id, one, two, three
FROM mytable m
where m.three = (SELECT max(m1.three) 
                 FROM mytable m1
                 WHERE m1.one = m.one
                   and m1.two = (SELECT max(m2.two)
                                 FROM mytable m2
                                 WHERE m2.one = m.one
                                )                
                )
  and two = (SELECT max(m3.two)
             FROM mytable m3
             WHERE m3.one = m.one)

code:

id   one   two  three
 9     1     3      3 
11     3     3      3 
12     2     3      1

[ Voor 5% gewijzigd door pjotrk op 17-11-2004 00:48 ]

[X-Lars]

Je hebt ook het juiste resultaat. Heb het snel getest en klopt als een bus. Ga ervan uit dat jouw oplossing veel sneller is. Ik had alleen (nogal cruciaal voor het resultaat eigenlijk, maar niet voor de uitwerking) een typefout gemaakt. Ik heb het in de TS aangepast. Het was:

code:

id  one two three
12   2   3    1

Zal er morgen nog eens wat beter naar kijken. Anyway, tnx!

[Priet]

Mag ik X-Lars bedanken voor het stellen van de vraag en pjotrk voor het beantwoorden van de vraag? Het was namelijk ook exact mijn probleem!

En het werkt meteen nu! Mijn dank is groot

[X-Lars]

Ik vind het principe ook wel iets voor de P&W FAQ - SQL. Het staat er niet in en het probleem is volgens mij vrij veelvoorkomend. Modje?