SQL: Rij met hoogste waarde per groep uitlezen

[SvMp]

Vraag over SQL (MySQL). Lastig te verwoorden in een topictitel, maar het probleem is als volgt, beschreven aan de hand van een concreet voorbeeld.

Stel ik heb een tabel met plaatsnamen. Deze zijn gekoppeld aan een plaats. Reden hiervoor is dat een plaats meerdere namen kan hebben. De namen-tabel heeft ook nog een veld status. Hoe hoger de status, hoe belangrijker de naam.

Tabel namen:

code:

ID (primary key)
Naam VARCHAR(30)
Plaats INTEGER (foreign key verwijst naar plaatstabel)
Status INTEGER

Het komt geregeld voor dat ik alle plaatsen wil uitlezen met de belangrijkste naam. Uiteraard alfabetisch gesorteerd.

SQL:

SELECT id, plaats, status, naam
FROM namen
GROUP BY plaats

Hiermee groepeer ik keurig per plaats.
Probleem is echter dat ik dan niet de belangrijkste naam heb. Ik krijg niet de juiste namen, maar een willekeurige naam (uiteraard wel van de plaats) voorgeschoteld.

Dus ik doe:

SQL:

SELECT plaats, MAX(status), naam
FROM namen
GROUP BY plaats

En dan blijkt er niks van id en naam te kloppen. De maximale status wordt opgevraagd, maar niet de bijbehorende naam.

Dat wordt zichtbaar met deze query:

SQL:

SELECT plaats, MAX(status), status, naam
FROM namen
GROUP BY plaats

code:

+-------------+-------------+--------+----------------+
| plaats    | max(status) | status | naam           |
+-------------+-------------+--------+----------------+
|          11 |         133 |     10 | Testje
|          12 |         129 |    129 | Testje
|          13 |         129 |    129 | Testje
|          14 |         130 |    130 | Testje
+-------------+-------------+--------+----------------+
4 rows in set (0,00 sec)

Ik heb het nog geprobeerd met een WHERE status=MAX(status) , maar dan krijg ik een foutmelding over onjuist gebruik van GROUP BY. Ook een ORDER BY vóór GROUP BY werkt niet. Er zou dus sortering op naam moeten plaats vinden afzonderlijk binnen elke groep (in dit geval alle rijen met dezelfde waarde voor 'plaats').

Ik zou een subquery kunnen doen, zo iets als "WHERE status=(SELECT MAX(status) FROM namen AS t2 WHERE t1.plaats=t2.plaats)" maar dat wil ik niet. Zou ook niet nodig moeten zijn, want het gaat om slechts één tabel en alle benodigde data komt wel een keer langs.

Is er een oplossing zonder subquery's?

[RobIII]

Als ik het niet heel verkeerd begrijp heb jij een HAVING-clause nodig Having status = max(status) of iets in die richting.

[ Voor 19% gewijzigd door RobIII op 12-05-2010 17:54 ]

[Verwijderd]

Ik heb het even uitgeprobeerd en ben vrijwel zeker dat je in dit geval niet ontkomt aan een subquery/join..

@hier boven:
Dat was inderdaad ook mijn eerste gedachte, maar dat lijkt niet te werken..

[ Voor 30% gewijzigd door Verwijderd op 12-05-2010 17:56 ]

[NMe]

Lees ook vooral Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk? even heel goed door, ik ben bang dat er iets over GROUP BY niet zo goed is blijven hangen bij je. Zelfs al staat MySQL dit toe; het feit dat je aangeeft dat je verkeerde data combineert laat al zien dat je het hier niet zo kan gebruiken.

[pedorus]

Dit lijkt me een klassiek groupwise max(imum) probleempje. Je zou daar makkelijk een aparte tag over kunnen vullen met soortgelijke topics.

RobIII schreef op woensdag 12 mei 2010 @ 17:53:
Having status = max(status)

Dat stukje sql kan nooit zinnig zijn..

[RobIII]

pedorus schreef op woensdag 12 mei 2010 @ 22:24:
Dat stukje sql kan nooit zinnig zijn..

Gooooooood morning
Wiedes; status in een aggregate en status zonder aggregate in de expressie gaat boem doen My bad