Language: Deutsch English















Last Update: 2017 - 03 - 20





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

von Philipp Stiefel, ursprünglich veröffentlicht 30. November 2015


Vögelsiloutten, article header image

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.

Screenshot der Tabellen und Beziehungen

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;

Ich speichere diese Abfrage unter dem Namen qryActors_Ratings.

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:

SELECT TOP 5 * FROM qryActors_Ratings ORDER BY Rating DESC

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

Screenshot der Ergebnisse der einfachen Abfrage

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

Diese Abfrage liefert das gewünschte Ergebnis.

Screenshot der Ergebnisse der Abfrage - Ohne Bindungen

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.

Screenshot der Ergebnisse der Abfrage - Mit Duplikaten

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.)

Screenshot der Ergebnisse der Abfrage - Basierend auf Durschnittswerten (AVG)

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;
Screenshot der Ergebnisse der Abfrage - Basierend auf der letzten Bewertung

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.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Abonniere meinen Newsletter

*

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.



© 1999 - 2016 by Philipp Stiefel