LIMIT ist limitiert – das müssen SQL-Entwickler vielfach feststellen. Die Klausel limitiert nicht nur die Anzahl auszugebender Datensätze, sie ist selbst syntaktisch engen Grenzen unterlegen, wobei es jedoch zwischen den verschiedenen Datenbankmanagementsystemen Unterschiede gibt.
LIMIT ist limitiert
Die Dokumentation von Oracle führt zu LIMIT das folgende aus:
The LIMIT clause is used to specify the maximum number M of results to return to the application. M is computed by an expression that may be a single integer literal, or a single external variable, or any expression which is built from literals and external variables and returns a single non-negative integer.
Bei MySQL ist im Rahmen des SELECT-Statements das Folgende nachzulesen:
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:
- Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
- Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.
Innerhalb eines einfachen SELECT-Statements ist, zumindest bei MySQL, keinerlei Dynamisierung mithilfe von Variablen oder anderweitigen Ausdrücken möglich. Wie Sie LIMIT dennoch Ihren Anforderungen entsprechend einsetzen können, möchte ich hier anhand verschiedener Beispiele zeigen. Ich verwende dazu die Sakila-Beispieldatenbank von MySQL.
Paginierung
Im einfachsten Fall, reduziert LIMIT die Anzahl der zurückzugebenden Datensätze eines SELECT-Statements auf eine konkret festgelegte Anzahl. Als Ausgangsbasis unserer Überlegungen sollen die Umsätze dienen, die pro Kunde aufgezeichnet wurden.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname; |
Die Abfrage ergibt 599 Datensätze. Da die Sakila-Datenbank gelegentlich modifiziert wird, kann bei Ihnen auch ein anderes Ergebnis resultieren. Wie dem auch sei, die einfachste Anforderung ist es, die Anzahl der anzuzeigenden Datensätze zu begrenzen.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname LIMIT 20; |
Das Statement zeigt die ersten 20 Datensätze der Ergebnismenge an.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname LIMIT 20, 20; |
Dieses Statement zeigt die nächsten 20 Datensätze an.
Das Limit-Statement hat zwei Parameter, offset und rowcount. offset ist optional. Wird kein Offset angegeben, so wird als Vorgabewert 0 angenommen. D.h., die Zählung der Datensätze ist null-basiert.
So, wie hier gezeigt, dient LIMIT der Paginierung der anzuzeigenden Datensätze.
Ranglisten
Die Paginierung mithilfe von LIMIT wird vorwiegend verwendet, um den Datenverkehr im Netzwerk zu begrenzen, also nicht immer gleich alle Daten einer Abfrage an den konsumierenden Client zu senden. Aus fachlicher Sicht ist LIMIT jedoch vor allem interessant, um Ranglisten abzubilden.
Wollen wir die zehn umsatzstärksten Kunden ermitteln, kombinieren wir LIMIT mit einer ORDER BY Klausel.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname ORDER BY SUM(payment.amount) DESC LIMIT 10; |
Das sieht für den Moment schon ganz gut aus.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
+----------+---------+--------+ | nachname | vorname | umsatz | +----------+---------+--------+ | SEAL | KARL | 221.55 | | HUNT | ELEANOR | 216.54 | | SHAW | CLARA | 195.58 | | KENNEDY | RHONDA | 194.61 | | SNYDER | MARION | 194.61 | | COLLAZO | TOMMY | 186.62 | | BULL | WESLEY | 177.60 | | CARY | TIM | 175.61 | | DEAN | MARCIA | 175.58 | | BRADLEY | ANA | 174.66 | +----------+---------+--------+ 10 rows in set (0,02 sec) |
Bei genauerem Hinsehen fällt jedoch auf, dass der Wert 194.61 doppelt auftritt. Soll die Abfrage ein Ranking ausdrücken, ist der vierte Platz doppelt vergeben. Die zehn ausgegebenen Datensätze decken also nur die Plätze 1 bis 9 ab. Platz 10 wird nicht ausgegeben. Idealerweise möchte man den aber auch ausgegeben haben. Das gilt unabhängig davon, wieviele Kunden sich einen Rang teilen. – Dieses Problem besteht bei allen Rankings, den Punkten bei Sportereignissen oder, oder, oder …
Wir brauchen eine Unterabfrage, welche uns, im aktuellen Fall, den Umsatz für Rang 10 bestimmt. Hierbei ist zu beachten, dass die Nummerierung, der Offset bei 0 beginnt (offset = 9). Um nun in einer WHERE- oder HAVING-Klausel mit einem einfachen Vergleichsoperator genutzt werden zu können, darf die Unterabfrage nur einen Datensatz liefern (rowcount = 1).
Hinweis: Wem die Zählung ab Null unheimlich ist, kann LIMIT 10, 1 im Statement schreiben, muss dann aber auf > statt >= vergleichen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname HAVING SUM(payment.amount) >= ( SELECT DISTINCT SUM(payment.amount) FROM payment GROUP BY payment.customer_id ORDER BY SUM(payment.amount) DESC LIMIT 9,1 ) ORDER BY SUM(payment.amount) DESC; |
Das LIMIT wurde in die Unterabfrage verschoben. Nun bekommen wir 11 Datensätze angezeigt, also die tatsächliche Ränge 1 bis 10.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
+----------+---------+--------+ | nachname | vorname | umsatz | +----------+---------+--------+ | SEAL | KARL | 221.55 | | HUNT | ELEANOR | 216.54 | | SHAW | CLARA | 195.58 | | KENNEDY | RHONDA | 194.61 | | SNYDER | MARION | 194.61 | | COLLAZO | TOMMY | 186.62 | | BULL | WESLEY | 177.60 | | CARY | TIM | 175.61 | | DEAN | MARCIA | 175.58 | | BRADLEY | ANA | 174.66 | | CARROLL | JUNE | 173.63 | +----------+---------+--------+ 11 rows in set (0,03 sec) |
Wie sieht es mit einem Ranking der umsatzschwächsten Kunden aus. LIMIT akzeptiert keine negativen Werte, um die auszugebenden Datensätze vom Ende her abzuzählen. Auch hier hilft die Sortierung.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname HAVING SUM(payment.amount) <= ( SELECT DISTINCT SUM(payment.amount) FROM payment GROUP BY payment.customer_id ORDER BY SUM(payment.amount) ASC LIMIT 9,1 ) ORDER BY SUM(payment.amount) DESC; |
Die zehn umsatzschwächsten Kunden in absteigender Sortierung:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
+----------+-----------+--------+ | nachname | vorname | umsatz | +----------+-----------+--------+ | STCLAIR | KIRK | 64.81 | | HOFFMAN | MATTIE | 64.78 | | MORALES | ANITA | 62.85 | | JORDAN | TIFFANY | 59.86 | | RIVERA | KATHERINE | 58.86 | | RUSSELL | ANNIE | 58.82 | | TURPIN | JOHNNY | 57.81 | | WYMAN | BRIAN | 52.88 | | OBRIEN | LEONA | 50.86 | | BOWMAN | CAROLINE | 50.85 | +----------+-----------+--------+ 10 rows in set (0,03 sec) |
Dazu haben wir lediglich die Sortierung der Unterabfrage und den Vergleichsoperator der HAVING-Klausel umkehren müssen. Hier sind es tatsächlich nur 10 Datensätze.
Dynamisches LIMIT
Kehren wir zu den Top-10 unserer umsatzstärksten Kunden zurück. Für die Top-20 müssen wir die LIMIT-Klausel anders parametrisieren. Schön wäre es, könnten wir Variablen dazu nutzen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SET @rang = 10; SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname HAVING SUM(payment.amount) > ( SELECT DISTINCT SUM(payment.amount) FROM payment GROUP BY payment.customer_id ORDER BY SUM(payment.amount) DESC LIMIT @rang,1 # FEHLER ) ORDER BY SUM(payment.amount) DESC; |
Beachten Sie, ich habe hier den Rang 10 angegeben. Ich habe daher den Vergleichsoperator angepasst. So oder so, ist das Statement aber fehlerhaft – zumindest in MySQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
DROP PROCEDURE IF EXISTS top; DELIMITER $$ CREATE PROCEDURE top(IN rang INT) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL BEGIN SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname HAVING SUM(payment.amount) > ( SELECT DISTINCT SUM(payment.amount) FROM payment GROUP BY payment.customer_id ORDER BY SUM(payment.amount) DESC LIMIT rang,1 ) ORDER BY SUM(payment.amount) DESC; END $$ DELIMITER ; CALL top(10); CALL top(20); |
Innerhalb gespeicherter Routinen sind Variablen jedoch erlaubt. Damit lassen sich dann auch beliebig komplexe Ausdrücke formulieren, deren Ergebnis in einer Variablen gespeichert und LIMIT übergeben wird.
Wer keine Routine schreiben möchte, kann es auch mit einem PREPARED STATEMENT versuchen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
PREPARE top FROM 'SELECT customer.last_name AS nachname , customer.first_name AS vorname , SUM(payment.amount) AS umsatz FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY nachname , vorname HAVING SUM(payment.amount) > ( SELECT DISTINCT SUM(payment.amount) FROM payment GROUP BY payment.customer_id ORDER BY SUM(payment.amount) DESC LIMIT ?,1 ) ORDER BY SUM(payment.amount) DESC;'; SET @rang = 10; EXECUTE top USING @rang; SET @rang = 20; EXECUTE top USING @rang; DEALLOCATE PREPARE top; |
Auch so lassen sich Abfragen mit LIMIT einfach dynamisch parametrisieren. Am Ende aller Tage sollte nicht vergessen werden, mit DEALLOCATE PREPARE die entsprechenden Ressourcen freizugeben.
Fazit
Auch wenn LIMIT in MySQL keine Variablen oder Ausdrücke unterstützt, ist es dennoch einfach das gewünschte Verhalten zu bewirken. LIMIT ist übrigens nicht auf das SELECT-Statement beschränkt. LIMIT kann auch mit allen anderen Statements, wie beispielsweise UPDATE oder DELETE, im Verbund eingesetzt werden.
