Thomas Wiedmann https://twiedmann.de
• Sie befinden sich hier: Startseite > SQL-Backstube > ORDER BY im Griff

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

11.09.2010: ORDER BY im Griff

Problemstellung

Daten sortieren ist eine besondere Stärke von SQL-Datenbanken. Sortiert wird die Ausgabe mit Hilfe des ORDER BY Befehls. Ohne ORDER BY ist die Ausgabereihenfolge (per Definition) nicht garantiert, auch wenn einige Datenbanken unter bestimmten Voraussetzungen (z. B. beim GROUP BY) bereits sortieren. Grundregel sollte immer sein, ohne ORDER BY ist die Reihenfolge der Ausgabe zufällig und nicht garantiert.


Schema der ORDER-BY-Klausel

Listing 1:

  1. [SQL-Abfrage]
  2. ORDER BY [sortiertliste]

Die Reihenfolge der Sortierung kann mit ASC (ascending) aufsteigend oder DESC (descending) absteigend beeinflußt werden.


DESC - absteigende Sortierung, der größte Wert steht oben.
ASC - (Default) aufsteigende Sortierung, der kleinste Wert steht oben

Wird weder DESC noch ASC als Sortierreihenfolge angegeben, kommt per Default ASC zum Zuge. ORDER BY steht (bis auf wenige Ausnahmen wie LIMIT, FOR UPDATE, .. ) als letzte Zeile in der SQL-Abfrage.


Getestet mit folgenden Datenbanken:

* IBM DB2 9.1
* MySQL 5.1.30
* ORACLE 10g 10.2 EE


Datenbank-Tabellen

Bei statistischen Auswertungen wird häufig nach den verschiedensten Kriterien sortiert. Den Zahlenjongleuren fallen immer spannende Fragen ein. Deshalb stammt unser Beispiel auch aus dem Controlling.

Listing 2:

  1. /**
  2. * Controlling
  3. */
  4. CREATE TABLE controlling (
  5. id INT NOT NULL,
  6. kunde_id INT NOT NULL,
  7. plz INT NOT NULL,
  8. umsatz DEC(10,2) NOT NULL,
  9. letzt_umsatz DATE NOT NULL,
  10. status CHAR(1) NOT NULL,
  11. PRIMARY KEY (id)
  12. );

Testdaten

Listing 3:

  1. /**
  2. * IBM DB2, MySQL Testdaten
  3. */
  4. INSERT INTO controlling VALUES
  5. ( 1, 1, 20000, 200, '2010-09-01', 'A' ),
  6. ( 2, 2, 20500, 100, '2010-09-01', 'C' ),
  7. ( 3, 3, 21000, 150, '2010-09-02', 'B' ),
  8. ( 4, 1, 20000, 90, '2010-09-05', 'C' ),
  9. ( 5, 2, 20500, 130, '2010-09-07', 'A' ),
  10. ( 6, 3, 21000, 180, '2010-09-09', 'B' ),
  11. ( 7, 1, 20000, 90, '2010-09-09', 'C' ),
  12. ( 8, 1, 20500, 30, '2010-09-10', 'A' ),
  13. ( 9, 3, 21000, 180, '2010-09-10', 'B' );

Listing 4:

  1. /**
  2. * ORACLE Testdaten
  3. */
  4. INSERT INTO controlling VALUES ( 1, 1, 20000, 200, TO_DATE('2010-09-01','YYYY-MM-DD'), 'A' );
  5. INSERT INTO controlling VALUES ( 2, 2, 20500, 100, TO_DATE('2010-09-01','YYYY-MM-DD'), 'C' );
  6. INSERT INTO controlling VALUES ( 3, 3, 21000, 150, TO_DATE('2010-09-02','YYYY-MM-DD'), 'B' );
  7. INSERT INTO controlling VALUES ( 4, 1, 20000, 90, TO_DATE('2010-09-05','YYYY-MM-DD'), 'C' );
  8. INSERT INTO controlling VALUES ( 5, 2, 20500, 130, TO_DATE('2010-09-07','YYYY-MM-DD'), 'A' );
  9. INSERT INTO controlling VALUES ( 6, 3, 21000, 180, TO_DATE('2010-09-09','YYYY-MM-DD'), 'B' );
  10. INSERT INTO controlling VALUES ( 7, 1, 20000, 90, TO_DATE('2010-09-09','YYYY-MM-DD'), 'C' );
  11. INSERT INTO controlling VALUES ( 8, 1, 20500, 30, TO_DATE('2010-09-10','YYYY-MM-DD'), 'A' );
  12. INSERT INTO controlling VALUES ( 9, 3, 21000, 180, TO_DATE('2010-09-10','YYYY-MM-DD'), 'B' );

SQL Auswertungen

Umsatzliste im PLZ-Bereich "21" für alle Kunden.

Listing 5:

  1. SELECT kunde_id, plz, umsatz
  2. FROM controlling
  3. WHERE plz BETWEEN 21000 AND 21999
  4. ORDER BY umsatz DESC;
  5. +----------+-------+--------+
  6. | kunde_id | plz | umsatz |
  7. +----------+-------+--------+
  8. | 3 | 21000 | 180.00 |
  9. | 3 | 21000 | 180.00 |
  10. | 3 | 21000 | 150.00 |
  11. +----------+-------+--------+
  12. 3 rows in set (0.02 sec)

Kumulierte Umsatzliste pro Kunde.

Listing 6: (nur MySQL)

  1. SELECT kunde_id, SUM(umsatz)
  2. FROM controlling
  3. GROUP BY kunde_id
  4. ORDER BY umsatz DESC;
  6. +----------+-------------+
  7. | kunde_id | SUM(umsatz) |
  8. +----------+-------------+
  9. | 1 | 410.00 |
  10. | 3 | 510.00 |
  11. | 2 | 230.00 |
  12. +----------+-------------+
  13. 3 rows in set (0.00 sec)

Während DB2 und ORACLE bei Listing 6: eine Fehlermeldung bringen, liefert MySQL eine Ausgabe. Leider ist das Ergebnis falsch! Die Ausgabe ist nicht korrekt nach der berechneten Summe des Umsatzes pro Kunde sortiert.

Korrekt kann die Abfrage auf folgende zwei Arten ausgeführt werden. Listing 7: sortiert nach der laufenden Spaltennummer in der SELECT-Liste. SUM(umsatz) ist die zweite Spalte, als ORDER BY 2.

Listing 7:

  1. SELECT kunde_id, SUM(umsatz)
  2. FROM controlling
  3. GROUP BY kunde_id
  4. ORDER BY 2 DESC;
  6. +----------+-------------+
  7. | kunde_id | SUM(umsatz) |
  8. +----------+-------------+
  9. | 3 | 510.00 |
  10. | 1 | 410.00 |
  11. | 2 | 230.00 |
  12. +----------+-------------+
  13. 3 rows in set (0.00 sec)

Oder alternativ kann der Aggregatspalte auch ein Alias-Namen gegeben werden, der dann auch wieder zur Sortierung verwendet werden kann.

Listing 8:

  1. SELECT kunde_id, SUM(umsatz) AS sum_umsatz
  2. FROM controlling
  3. GROUP BY kunde_id
  4. ORDER BY sum_umsatz DESC;
  6. +----------+------------+
  7. | kunde_id | sum_umsatz |
  8. +----------+------------+
  9. | 3 | 510.00 |
  10. | 1 | 410.00 |
  11. | 2 | 230.00 |
  12. +----------+------------+
  13. 3 rows in set (0.00 sec)

Umsatzliste nach Status und Umsatz. Wobei der Status in der Reihenfolge "B", "A", "C" und aufsteigendem Umsatz ausgegeben werden soll. Mit CASE wird temporär eine eigenständige Sortierreihenfolge erzeugt. Jedem Status wird ein Zahlenwert zugewiesen und damit die gewünschte Sortierung ermöglicht.


Listing 9:

  1. SELECT kunde_id, status, umsatz
  2. FROM controlling
  3. ORDER BY CASE status
  4. WHEN 'B' THEN 3
  5. WHEN 'A' THEN 2
  6. WHEN 'C' THEN 1
  7. END DESC,
  8. umsatz;
  10. +----------+--------+--------+
  11. | kunde_id | status | umsatz |
  12. +----------+--------+--------+
  13. | 3 | B | 150.00 |
  14. | 3 | B | 180.00 |
  15. | 3 | B | 180.00 |
  16. | 1 | A | 30.00 |
  17. | 2 | A | 130.00 |
  18. | 1 | A | 200.00 |
  19. | 1 | C | 90.00 |
  20. | 1 | C | 90.00 |
  21. | 2 | C | 100.00 |
  22. +----------+--------+--------+
  23. 9 rows in set (0.00 sec)

Täglichen Status- und Aktionscode auf den Umsatz anwenden und Rabatte berücksichtigen. Als Zeitraum gilt der letzte Umsatz ab 01.09.2010 bis heute.


Listing 10:

  1. /**
  2. * DB2, MySQL
  3. */
  4. SELECT letzt_umsatz, AVG(CASE status
  5. WHEN 'B' THEN 0.3 * umsatz
  6. WHEN 'A' THEN 0.15 * umsatz
  7. WHEN 'C' THEN 0.10 * umsatz
  8. END) AS avg_code_umsatz
  9. FROM controlling
  10. WHERE letzt_umsatz BETWEEN '2010-09-01' AND CURRENT_DATE
  11. GROUP BY letzt_umsatz
  12. ORDER BY 2;
  14. /**
  15. * ORACLE
  16. */
  17. SELECT letzt_umsatz, AVG(CASE status
  18. WHEN 'B' THEN 0.3 * umsatz
  19. WHEN 'A' THEN 0.15 * umsatz
  20. WHEN 'C' THEN 0.10 * umsatz
  21. END) AS avg_code_umsatz
  22. FROM controlling
  23. WHERE letzt_umsatz BETWEEN TO_DATE('2010-09-01','YYYY-MM-DD') AND CURRENT_DATE
  24. GROUP BY letzt_umsatz
  25. ORDER BY 2;
  27. +--------------+-----------------+
  28. | letzt_umsatz | avg_code_umsatz |
  29. +--------------+-----------------+
  30. | 2010-09-05 | 9.00000000 |
  31. | 2010-09-07 | 19.50000000 |
  32. | 2010-09-01 | 20.00000000 |
  33. | 2010-09-10 | 29.25000000 |
  34. | 2010-09-09 | 31.50000000 |
  35. | 2010-09-02 | 45.00000000 |
  36. +--------------+-----------------+
  37. 6 rows in set (0.00 sec)

Resumee

Mit ORDER BY kann die Ausgabe der SQL-Abfragen sehr flexibel gesteuert werden. Speziell in der Kombination mit dem CASE Befehl können spezielle Anforderungen abgedeckt werden. Unschön ist, dass MySQL beim obigen Beispiel (Listing 6:) leider falsche Ergebnisse erzeugt. Wie immer ist die unterschiedliche Auslegung des SQL-Standards, der drei gezeigten Datenbanken, auch diesmal wieder ein Thema.


https://twiedmann.de


Sitemap - Inhaltsverzeichnis

© 2002-2016 by Thomas Wiedmann : (Stand : 11.01.2015).