[MySQL] Groupwise maximum

[Spinal]

De situatie: ik probeer een vriendensysteem te bouwen, dus gebruikers kunnen andere gebruikers toevoegen als vriend.
Ik heb een tabel users met de kolommen id, name en emailid. id is uiteraard een uniek nummer, emailid kan meerdere keer voorkomen. Het hoogste id is het actieve account van een gebruiker.
Verder dus een tabel [b]friends[b] met de kolommen emailid1 en emailid2

Nou wil ik voor een gebruiker kijken welke vrienden deze persoon heeft, hierbij moet uiteraard het actieve account komen, dus het hoogste id.

Stel, de persoon heeft emailid 10, dan heb ik deze voorlopige query:

SQL:

SELECT MAX(users1.id) AS uid1, users1.name AS name1, MAX(users2.id) AS uid2, users2.name AS name2
FROM friends
INNER JOIN users AS users1 ON friends.emailid1 = users1.emailid
INNER JOIN users AS users2 ON friends.emailid2 = users2.emailid
WHERE emailid1 =10
OR emailid2 =10
GROUP BY emailid1,emailid2

Hier komen prima de id's en namen uit van de gebruikers, maar zoals verwacht niet de goede namen bij de id's. En hier begint het plezier van de groupwise maximum
Ik heb gezocht op GoT naar een oplossing en het antwoord was bijna altijd "lees je eens in over groupwise maximum". Ik was al zover dat ik wist dat het daar op uit zou komen maar ik vind weinig goede uitleg over hoe ik dit moet aanpakken. Deze link is vrij uitgebreid, maar ik zie daar door de bomen het bos niet meer. 10 voorbeelden waarvan ik vaak niet zie waarom het werkt en hoe ik dit kan toepassen op een dubbele join.
Bij MySQL zelf kom ik ook niet veel verder.

Ik heb al het een en ander geprobeerd:

SQL:

SELECT friends.emailid,emailid2,MAX(users1.id AS id1),users1.name AS name1,MAX(users2.id) AS id2,users2.name AS name2
FROM friends
LEFT JOIN users AS users1 ON friends.emailid1=users1.emailid
LEFT JOIN users AS users2 ON friends.emailid2=users2.emailid
WHERE (users1.id=(SELECT MAX(id) FROM users WHERE emailid=10)
OR users2.id=(SELECT MAX(id) FROM users WHERE emailid=10))
AND (friends.emailid1=10 OR emailid2=10)
GROUP BY emailid1,emailid2

levert hetzelfde resultaat als hierboven.

SQL:

SELECT
    emailid1,emailid2,
    users1.id AS uid1,users1.name AS name1,
    users2.id AS uid2,users2.name AS name2
FROM friends
LEFT JOIN users AS users1
ON friends.emailid1=users1.emailid
LEFT JOIN users AS users2
ON friends.emailid2=users2.emailid
WHERE users1.id=(
    SELECT MAX(id)
    FROM users AS users1
) OR users2.id=(
    SELECT MAX(id)
    FROM users AS users2
)
AND (
    emailid1=10
OR
    emailid2=10
)

levert geen rijen op.

Ik zoek uiteraard geen kant-en-klare oplossing, ik wil graag weten wat ik fout doe en hoe ik dit moet oplossen. Maak ik een denkfout? Snap ik het gewoon niet goed genoeg?
Leesmateriaal en zetjes in de goede richting zijn van harte welkom

[pedorus]

Ik snap die eerste query al niet helemaal. Je wordt vrienden met een emailid, wat een groep gebruikers is? Maar dan wil je toch evengoed alle vrienden zien, en niet steeds de laatste per groep? En het wordt ook nog eens lastig bepalen wie je zelf eigenlijk was...

Heb je \[PHP/SQL] A.d.h.v. koppeltabel vrienden bepalen gelezen?

[Spinal]

Ik kan me voorstellen dat het allemaal wat vaag klinkt, maar je wordt inderdaad vrienden met een emailid, wat je zou kunnen zien als een groep gebruikers, maar ze horen allemaal bij 1 persoon en alleen de laatste is van belang.
Achterhalen wie je zelf bent is niet zo lastig, want je eigen emailid is uiteraard bekend

Dat topic ben ik niet tegengekomen (verkeerde zoekwoorden, zal nog eens zoeken naar koppeltabel en vrienden) dus dat zal ik eens doorlezen!

-edit-
overigens zou ik, mocht het niet anders kunnen, ook alle data dubbel in de tabel zetten, dus in het geval van emailid's 10 en 20 kan ik 1=10/2=20 en 1=20/2=10 opslaan. Dat zou een join schelen en die paar extra bytes opslaan maakt weinig uit...

[ Voor 21% gewijzigd door Spinal op 21-05-2010 14:09 ]

[Voutloos]

Waarom heeft een gebruiker meerdere accounts? Heeft dat nog nut of is dat puur omdat je het leuk vind om al je queries 42x zo complex te maken?

[Spinal]

Haha, terechte vraag inderdaad het gaat om een online spel waarin mensen dood kunnen gaan en weer terug kunnen komen met een nieuw account maar op hetzelfde emailid. In dit geval is het de bedoeling dat je vrienden houdt nadat je dood bent gegaan en opnieuw geregistreerd bent.

Maar zelfs als deze query niet gaat lukken (als het écht niet anders kan laat ik er wel een 2e query op los om de namen op te halen) wil ik toch graag weten hoe ik de groupwise maximum voor elkaar krijg.

Als je er van uit gaat dat alle data dubbel in de tabel staat (zie edit vorige bericht) dan krijg ik het nog niet voor elkaar. Ik heb gister de volgende query geprobeerd aan te passen (de eerste link in de openingspost):

SQL:

SELECT continent, name, population
FROM Country
WHERE ROW(population, continent) IN (
       SELECT MAX(population), continent
         FROM Country
        GROUP BY continent);

Maar daarop liep MySQL "vast" en kon ik pas na de timeout van MySQL verder gaan...

[Voutloos]

Negeer eens eerst het detail mbt de wederzijdse vriendschappen. Oftewel: doe eerst bijv. de query voor alle relaties waar de emailid 10 in de 1e kolom staat.

Die query zal een meer straightforward zijn (aka: ik zie hem al helemaal voor me ) en vervolgens kan je zelf bepalen hoe je hem uitbreidt: Dmv allemaal disjuncties (je huidige poging), of gewoon dmv een union distinct. Dat laatste zou zo maar een stuk simpeler kunnen zijn, en mogelijk nog sneller ook.

Je wilt nu alles in 1x oplossen, dus als er uberhaupt iets vastloopt ben jij het.

[Spinal]

Oke, naar aanleiding van de eerste link en wat je zei heb ik het volgende:

SQL:

SELECT users.emailid, name, users.id
FROM friends
LEFT JOIN users ON friends.emailid2 = users.emailid
WHERE users.id = (
  SELECT MAX(id) AS maxid
  FROM users AS u2
  WHERE u2.emailid = users.emailid
)
AND friends.emailid1 =11

Ik weet niet of het optimaal is, maar het werkt en lijkt vrij snel te zijn de union distinct lijkt het ook goed te doen. Bedankt voor je hulp!