Access SQL - Select Top X Datensätze – Ohne Bindungen

In den letzten Wochen habe ich ein und dieselbe Frage in mehreren Foren gelesen. „Ich möchte mit einer Abfrage die ersten X Datensätze, sortiert nach Y ermitteln. Aber wenn sich Datensätze mit gleichen Werten darunter befinden, ergibt meine Abfrage mehr als die gewünschte Anzahl von Datensätzen. Ich möchte genau X Datensätze, nicht mehr.“

In diesem Artikel zeige ich, wie das in Microsoft Access mit relativ einfachen SQL Abfragen erreicht werden kann.

Voraussetzungen

Ich die Abfragen in diesem Artikel basieren alle aus zwei einfachen Beispieltabellen mit Schauspielern (tblActors) und ihren Bewertungen (tblActorRatings), die von einer willkürlichen Instanz (mir ;-)) festgelegt wurden.

Als nächste Voraussetzung brauchen wir eine einfache Abfrage, die die Schauspieler mit ihren Bewertungen zusammenführt. Hier ist sie:

SELECT a.ActorId, a.ActorName, ar.Rating, ar.DateOfRating FROM tblActors a INNER JOIN tblActorRatings ar ON a.ActorId = ar.ActorId;

Einfache Lösung

Die grundlegende Anforderung die X am besten bewerteten Schauspieler abzufragen ist einfach zu lösen. Die Access Jet/Ace-Engine (die Datenbank Engine von Access) unterstützt das TOP Prädikat der SQL Abfragesprache. Also um nur die obersten X Datensätze abzufragen, schreiben wir eine Abfrage wie diese:

Diese Abfrage liefert die 5 am besten bewerteten Schauspieler aus unseren Tabellen. So weit war das einfach.

Aber wenn wir uns das Ergebnis anschauen, wir ein Problem deutlich. In unserem Beispiel-Datenbestand sind mehrere Schauspieler mit einer Bewertung von 4. Alle sind in unserem Abfrageergebnis enthalten. Also, wie bestimmt bereits bemerkt hast, liefert unsere Abfrage nicht 5 Datensätze, sondern 6.

Keine Bindungen

Um dieses Problem zu lösen, müssen wir über ein weiteres Merkmal sortieren damit wir eine eindeutige Reihenfolge ohne Bindungen erhalten. Die technische Anforderung an dieses Merkmal ist Ein-Eindeutigkeit. Diese Anforderung wird vom Primärschlüssel der Tabelle (tblActors) erfüllt.

Einfach nur zusätzlich über den Primärschlüssel zu sortieren würde bereits die technische Anforderung erfüllen, aber es könnte Rückfragen der Benutzer auslösen. Wie „Warum ist Schauspieler A in dem Ergebnis enthalten, aber Schauspieler B nicht?“ Daher empfehle ich, eine Regel zu verwenden, die einfach zu erklären ist, wie „Neuere Bewertungen werden höher gewichtet.“

Um sicher zu gehen, dass es auch dann keine Bindungen gibt, wenn zwei Bewertungen das gleiche Datum haben, sollten wir zusätzlich noch den Primärschlüssel als drittes Sortierungsfeld einschließen.

SELECT TOP 5 * FROM qryActors_Ratings ORDER BY Rating DESC, DateOfRating DESC, ActorId DESC

Mehrere Bewertungen pro Schauspieler

Das hätte eben das Ende des Artikels sein können. Wenn du aber genau auf mein Tabellendesign geachtet hast, könntest du bereits bemerkt haben, dass es mehr als eine Bewertung für einen Schauspieler geben kann. Dies kann dann dazu führen, dass ein Schauspieler mehrfach in den Ergebnissen enthalten ist. – Ich habe mal eine Bewertung hinzugefügt, die dieses Problem verdeutlicht.

Wenn es deine Datenstruktur ebenfalls zulässt, dass diese Problemsituation auftritt, musst du dich für eine Methode entscheiden, um diese Duplikate aus den Ergebnissen zu entfernen. Ich sehe in diesem Kontext zwei mögliche Optionen dafür.

Option 1 – Basierend auf dem Durchschnittswert der Bewertungen

Du änderst unsere Abfrage qryActors_Ratings von vorher, um die durchschnittliche Bewertung eines Schauspielers zu ermitteln. Das ist einfach mit der AVG Domänenaggregatfunktion zu lösen.

SELECT a.ActorId, a.ActorName, AVG(ar.Rating) AS Rating, CDate(AVG(ar.DateOfRating)) AS DateOfRating FROM tblActors AS a INNER JOIN tblActorRatings AS ar ON a.ActorId = ar.ActorId GROUP BY a.ActorId, a.ActorName;

Ich habe die AVG Funktion ebenfalls auf die Spalte DateOfRating angewendet. Der daraus resultierende Durchschnittswert ist allerdings eine Zahl und kein Datum, deshalb müssen wir diese mit der CDate-Funktion wieder in ein Datum umwandeln. (Das funktioniert, weil Datumswerte intern als Zahlen gespeichert werden.)

Unsere Abfrage um das endgültige Ergebnis, die besten 5, zu ermitteln kann dann unverändert bleiben. – Nur die RatingId kann darin nicht mehr enthalten sein, weil jede einzelne Zeile der Ergebnisse auf mehreren Rating-Datensätzen basieren kann.

Option 2 – Basierend auf der letzten Bewertung

Die andere plausible Methode, um die 5 Besten zu ermitteln, ist basierend auf der letzten (oder analog auch der ersten) Bewertung, die für jeden Schauspieler gespeichert ist. In dieser Abfrage verwenden wir die MAX Domänenaggregatfunktion, um die höchste RatingId aus der Tabelle tblActorRatings zu ermitteln. – Sie dir bewusst, dass dies nicht notwendigerweise die neuste Bewertung anhand des Datums DateOfRating ist, sondern der Wert, der zuletzt in unserer Tabelle eingegeben wurde. (Weil die RatingId ein AutoWert-Feld ist.)

SELECT TOP 5 qar.* FROM qryActors_Ratings AS qar WHERE RatingId =(SELECT MAX(ar.RatingId) FROM tblActorRatings ar WHERE ar.ActorId = qar.ActorId) ORDER BY Rating DESC, DateOfRating DESC, ActorId DESC;

Es mag in manchen Fällen noch kompliziertere Anforderungen geben, aber ich denke diese beiden Optionen sind ein guter Startpunkt, von dem aus man in den meisten Szenarios zu einer Lösung kommen kann.

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.
Die Emailliste wird bei Mailchimp in den USA gespeichert. Diese Auftragsverarbeitung ist vertraglich geregelt. Weitere Details in der Datenschutzerklärung.

Vorteile des Newsletter-Abos