[SQL] top 5 resultaten optellen

[mstege]

Voor een klassement moet ik de beste 5 resultaten uit 8 ritten optellen. Ik heb hiervoor twee tabellen gemaakt; namelijk:

code:

Persoonsgegevens:
+----+------------+-----------+
| id | naam       | categorie |
+----+------------+-----------+
|  1 | iemand     | hc1       |
|  2 | nog iemand | hc2       |
+----+------------+-----------+

code:

Ritgegevens:
+----+-----+--------+
| id | rit | punten |
+----+-----+--------+
|  1 |   1 |     20 |
|  2 |   1 |     19 |
|  1 |   2 |      0 |
|  2 |   2 |     23 |
|  1 |   3 |     40 |
|  2 |   3 |     39 |
|  1 |   4 |     40 |
|  2 |   4 |     34 |
|  1 |   5 |     19 |
|  2 |   5 |     40 |
|  1 |   6 |     21 |
|  2 |   6 |     39 |
|  1 |   7 |     39 |
|  2 |   7 |     39 |
|  1 |   8 |     38 |
|  2 |   8 |     39 |
+----+-----+--------+

Wat ik dus wil is dat hij voor elke persoon de beste 5 resultaten opgeteld:

code:

Resultaat:
+----+------------+---------+
| id | naam       | Beste 5 |
+----+------------+---------+
|  1 | iemand     | 177     |
|  2 | nog iemand | 196     |
+----+------------+---------+

Dit dus, maar ik weet niet hoe ik dit met een SQL statement kan oplossen

Het is trouwens voor een access database

[CrashOne]

Wat heb je al geprobeerd?

SELECT SUM(FOO) FROM BLAAT WHERE IETS.BLAAT = IETS.BLAAT2

Zo iets dan he

[ Voor 11% gewijzigd door CrashOne op 10-07-2004 14:22 ]

[NMe]

Met SUM en TOP kom je een heel eind.

[mstege]

Het zal inderdaad met SUM en TOP moeten, zover was ik al (sorry, had ik moeten vermelden).

Je krijgt dan een query zoals:

SELECT Sum(b.punten) AS Sommetje
FROM (SELECT TOP 5 a.id, b.id, b.rit, b.punten FROM Persoonsgegevens a, Ritgegevens b WHERE a.id = b.id ORDER BY punten DESC)

Het resultaat van deze query is echter het getal 315.

Het probleem bestaat uit twee delen:

Het eerste probleeem is dat de subquery geeft als resultaat

code:

+------+------+-----+-----------+
| a.id | b.id | rit | punten    |
+------+------+-----+-----------+
|   1  |   1  |  4  |    40     |
|   1  |   1  |  3  |    40     |
|   2  |   2  |  5  |    40     |
|   2  |   2  |  7  |    39     |
|   1  |   1  |  7  |    39     |
|   2  |   2  |  6  |    39     |
|   2  |   2  |  8  |    39     |
|   2  |   2  |  3  |    39     |
+------+------+-----+-----------+

Waar ik graag het volgende resultaat had gezien

code:

+------+------+-----+-----------+
| a.id | b.id | rit | punten    |
+------+------+-----+-----------+
|   1  |   1  |  4  |    40     |
|   1  |   1  |  3  |    40     |
|   1  |   1  |  7  |    39     |
|   1  |   1  |  8  |    38     |
|   1  |   1  |  1  |    20     |
|   2  |   2  |  5  |    40     |
|   2  |   2  |  7  |    39     |
|   2  |   2  |  6  |    39     |
|   2  |   2  |  8  |    39     |
|   2  |   2  |  3  |    39     |
+------+------+-----+-----------+

Dus met twee extra rijen, zodat niet de hoogste 5 resultaten tevoorschijn komen, maar de hoogste 5 per id. Ik weet niet hoe ik dit voor elkaar krijg, DISTINCT levert (nog?) niet het juiste resultaat op.

Het tweede probleem is dat de SUM op deze manier 1 waarde teruggeeft, terwijl ik graag 1 waarde per id terug zou willen

[Zoijar]

je moet 'group by' gebruiken op id...

[mstege]

Die GROUP BY is inderdaad een goede oplossing voor het tweede probleem.

Als ik deze in de subquery gebruik krijg ik nu het volgende resultaat

SELECT a.id, SUM(punten) AS[Beste 5] FROM (SELECT TOP 5 a.id, b.id, b.punten FROM Persoonsgegevens a, Ritgegevens b WHERE a.id = b.id ORDER BY punten DESC) GROUP BY a.id

code:

Resultaat:
+----+---------+
| id | Beste 5 |
+----+---------+
|  1 | 119     |
|  2 | 196     |
+----+---------+

Voor id 1 geeft hij dus niet het juiste resultaat, omdat de subquery nog niet helemaal de juiste waarden teruggeeft. Deze geeft nu namelijk alleen de 5 hoogste waarden van allemaal, terwijl hij de hoogste 5 per id zou moeten geven

Hij geeft dan 119 en niet 177, wat het juiste resultaat zou zijn geweest

[ Voor 76% gewijzigd door mstege op 10-07-2004 18:42 . Reden: Beetje verkeerd gewijzigd, dubbel gepost ]

[nescafe]

Je moet eigelijk je tabel Persoonsgegevens buiten de subquery halen en deze wel aanhalen in de where-expressie binnen de subquery. Helaas ondersteunt mijn versie van access dit niet.

Ik heb ook een query '5hoogste' gemaakt die een TOP 5 uitstuurt met persoonsId:

code:

SELECT TOP 5 Ritgegevens.punten, Ritgegevens.pId
FROM Ritgegevens
ORDER BY Ritgegevens.punten DESC;

en deze in de volgende query op proberen te nemen:

code:

SELECT Persoonsgegevens.Naam, DSum("punten","5hoogste","pid= " & [id])
FROM Persoonsgegevens

Maar helaas. Deze geeft ook 119/196. Omdat de where-expressie van DSum pas ná het uitvoeren van query '5hoogste' wordt uitgevoerd. Dit kun je ook nakijken met DCount, voor persoon 1 vindt hij 3 records en voor persoon 2 5.

Aka.. nog geen oplossing in access. In mysql heb ik het eerder opgelost, dit is na te lezen in mijn doodgeschopte topic [rml][ SQL] Maximum aantal rows per unieke FK[/rml].

[BrZ]

Het kan met subqueries door in 5 stappen de hoogste 5 van elke id te pakken. Je moet dan elke keer de de score nemen die lager is dan de vorige. Hierdoor krijg je een idiote lijst met subqueries, wat ik dus niet zou aanraden.

De enige goede oplossing is door gebruik te maken van variabelen in queries, alhoewel ik in zulke gevallen snel geneigd zal zijn om het buiten de database om op te vangen (hoe je dat in access zou kunnen doen weet ik echter niet), alhoewel dat natuurlijk niet netjes is.