Sortierung

Collation und SQL-Klauseln order by, group by

Auf dieser Seite werden einige Hinweise zur Sortierung von SQL Ergebnis-Daten vorgestellt. Insbesondere die Wirkung der verschiedenen Sortier-Regeln (Collations) ist wenig bekannt.
Standard Query Language SQL Die Datenbank-'Sprache' (Standard Query Language)
Sortierung Übersicht
Zeichensatz Auswahl und Reihenfolge der verwendbaren Zeichen
Collation Auswahl von Sortier-Regeln, abhängig vom Zeichensatz
SQL Sortierung Die SQL-Anweisung   order by
SQL Gruppierung Die SQL-Anweisung   group by

Sortierung
Das Sortieren von Daten erscheint auf den ersten Blick trivial.
Bei genauer Betrachtung stößt man jedoch auf einige Besonderheiten. 		Das Thema hängt mit der Codierung von Zeichen in der Informatik (↓ Zeichensätze) und - unabhängig davon - mit nationalen oder sogar lokalen Gewohnheiten (↓ Collation) zusammen.

Zeichensatz

Wenn man Text (Zeichenketten, Strings) in einer Datenbank (DB) speichert, dann muss man sich für eine Form der Codierung entscheiden. Diese Entscheidung sollte man bereits bei Festlegung der Daten-Struktur treffen, d.h. vor der Eingabe von Daten.
Jede Datenbank bietet eine Auswahl verschiedener wählbarer Feld-Typen. Wenn man eine der String-Typen wählt (z.B. VARCHAR, TEXT, ...), dann kann man zusätzlich den ↓ Zeichensatz wählen, mit welchem die Zeichen in diesem Feld gespeichert werden.

Collation

Je nach Kulturkreis haben sich historisch unterschiedliche Sortier-Gewohnheiten etabliert. Jede Datenbank bietet für Text-Felder eine Auswahl unterschiedlicher Sortier-Regeln (↓ Collation).
Diese Entscheidung hat eine wichtige Bedeutung für die Praxis: Man sollte alle denkbaren Vorkehrungen treffen, damit gesuchte Daten auch an jene Stelle gereiht werden, wo sie von den AnwenderInnen erwartet werden.

SQL-Syntax

Mit der SQL Anweisung select werden Daten gesucht, mit den Zusatz-Klauseln order by oder group by werden die gefundenen Datensätze sortiert und/oder in Gruppen zusammengefasst.
Dazu werden einige Beispiele vorgestellt.
Fortgeschrittene EntwicklerInnen können weitere Möglichkeiten nutzen:
Man kann aus dem Quelltext eine individuelle Version der Datenbank-Software compilieren und dabei bestimmte Zeichensätze einbinden.
Man kann eigene Sortier-Regeln (Collations) erstellen und mit der Datenbank verwenden. 		Hinweise zu diesen und anderen speziellen Optionen finden sie in der Original Dokumentation, in speziellen EntwicklerInnen-Dokumenten und -Foren.

Zeichensatz

Bitmuster

Zeichen (Buchstaben, Ziffern, Sonderzeichen, ...) werden in der Informatik wie alle anderen Daten intern als Bitmuster gespeichert. Man braucht eine Vereinbarung (Daten-Typ), um ein Bitmuster als 'Daten' zu verstehen. Ein Bitmuster ohne Kenntnis des Daten-Typs ist vollkommen wertlos.
Beispiel: Ein Byte (Muster aus 8 Bits) kann eine ganze Zahl mit oder ohne Vorzeichen darstellen, oder einen Teil einer Gleitkomma-Zahl, oder einen Buchstaben, oder einen Teil eines Bildes, oder einen Teil eines Programms, ...

Zeichensatz

Weltweit werden sehr viele verschiedene Zeichen verwendet. Ihre Auswahl und Anordnung ist willkürlich: Die Reihenfolge ist nicht vorgegeben (wie z.B. bei ganzen Zahlen) sondern man kann die gewählten Zeichen beliebig anordnen. Die meisten Zeichensätze wurden tatsächlich unsystematisch definiert, z.B. nach persönlichem Gutdünken oder politischer Opportunität.

Das 'Alphabet' bezeichnet zwar eine anerkannte, historisch gewachsene Reihenfolge, aber es umfasst nur eine Auswahl der wenigen 'Lateinischen' Großbuchstaben.
Kleine Buchstaben, Ziffern, Leerzeichen, Interpunktionen und Klammern, die Vielzahl der nationalen Sonderzeichen sind davon ebenso wenig betroffen wie z.B. die von Milliarden Menschen verwendeten Zeichen asiatischer Sprachen.

In der Informatik verwendet man Verweis-Tabellen (Lookup-Tables) zur Umwandlung (Codierung und Decodierung) beliebiger Daten (Zeichen, Farben, Töne, ...) in Bitmuster und umgekehrt. Verweis-Tabellen zur Codierung von Zeichen werden → Zeichensätze genannt.

♦ Details zu Zeichensätzen
Jeder → Zeichensatz codiert eine willkürliche Auswahl und Anordnung von Zeichen. Das hat u.a. folgende Probleme zur Folge:

• Ein Zeichen kann in einem Zeichensatz enthalten sein, oder es kann darin fehlen. In letzterem Fall gibt es kein Bitmuster zur Darstellung des Zeichens.

• Ein Bitmuster kann je nach Zeichensatz unterschiedliche Zeichen bedeuten. Umgekehrt kann das gleiche Zeichen in verschiedenen Zeichensätzen mit unterschiedlichen Bitmustern codiert werden.

• Ein Computer kann ein Bitmuster nur dann korrekt interpretieren, wenn der gewünschte Zeichensatz verfügbar ('geladen') ist, und wenn ihn das jeweilige Programm auch verwenden kann. Es ist z.B. nicht selbstverständlich, dass ein Programm zwischen verschiedenen Zeichensätzen umschalten kann.

ASCII

Der wichtige Zeichensatz 'American Standard for Information Interchange' (ASCII) legt die Codierung von 95 'druckbaren' Zeichen fest.
In der IT-Entwicklung (Programmierung, Quelltext aller Programmiersprachen) wird ausschließlich ASCII verwendet.
Dieser Zeichensatz ist weltweit in jedem PC-Betriebssystem verfügbar und verwendet eine Verweis-Tabelle von 128 Zeilen (7 Bit). Der ASCII-Zeichensatz ist als Teil-Menge in den meisten anderen Zeichensätzen enthalten.

Dieser Zeichensatz orientiert sich historisch am Bedarf der USA, daher fehlen die Sonderzeichen anderer Sprachen, z.B. die deutschen ÄÖÜäöü߀

♣ Tipp: Wenn sie in einem Datenbank-Feld ausnahmsweise nur ASCII-Text verwalten wollen, dann ist die Wahl des ASCII-Zeichensatzes vorteilhaft: Er braucht wenig Platz, lässt sich rasch durchsuchen und sortieren und problemlos in andere Zeichensätze umwandeln.

• Wenn ein Zeichen im jeweiligen Zeichensatz nicht definiert ist, wird es von MySQL als ? gespeichert. Das Wort 'Größe' wird daher in ein ASCII-Textfeld als 'Gr??e' eingetragen. Die SQL-Anweisung ergibt eine Warnung, jedoch keinen Fehler.

ISO-8859 (Latin)

Dieser Standard ist u.a. in Europa weit verbreitet und umfasst 16 verschiedene Zeichensätze in Verweis-Tabellen von je 256 Zeilen (8 Bit). Im deutschsprachigen Raum wird oft der Standard → ISO-8859-1 unter dem Namen Latin-1 verwendet. Auch die meisten Datenbanken verwenden dafür Bezeichnungen ähnlich latin1

Diese Zeichensätze waren für den nationalen Bedarf ausreichend, sind jedoch heute nicht mehr zeitgemäß. Einige typische Mängel:
• Das €-Zeichen ist nicht erfasst.
• Es ist unmöglich, die in der EU oder zumindest die in den Nachbarstaaten benötigten Zeichen gemeinsam zu verwenden. In Latin-1 fehlen u.a. die slawischen, griechischen, kyrillischen ... Zeichen.
• Das gleiche Bitmuster ergibt je nach ISO-Zeichensatz unterschiedliche Zeichen. Das Muster 11010100 (Hexadezimal C4) kann z.B. eines der Zeichen ÄУؤΔฤ bedeuten.

♣ Tipp: Nicht für neue Entwicklungen. Vorhandene Daten in ↓ Unicode umwandeln.

Unicode

Dieser Standard Zeichensatz entspricht als einziger dem 'Stand der Technik'. Von den theoretisch 17 Mio (24 Bit) oder 4 Mia (32 Bit) Zeichen werden die 65000 (16 Bit) meist-verwendeten von allen gängigen Betriebssystemen unterstützt, darunter alle in Europa, Nord- und Südamerika und zumindest die wichtigsten in Asien verwendeten Zeichen. Man kann → Unicode u.a. in allen gängigen Datenbanken und Textverarbeitungs-Programmen und auf allen Webseiten verwenden.

Als Besonderheit werden Unicode-Zeichen nicht mit jenem Bitmuster gespeichert, welches in den Unicode Verweis-Tabellen festgelegt ist, sondern in einer von mehreren wählbaren Codier-Formen.

♣ Tipp: Wählen sie die meist-verwendete Codier-Form → UTF-8, und zwar nicht nur für die Text-Felder von Datenbanken sondern auch für alle Webseiten und sonstigen Dokumente. Diese Entscheidung befreit ihre Daten von lokalen Einschränkungen und von den ansonsten aufwändigen Code-Umwandlungen.

UTF-8

Ist die meist-verwendete Unicode Speicher-Variante.
Sie wird für alle Datenbank-Felder empfohlen, in denen Text verwaltet wird.

♣ Tipp: Verwenden sie auch für die dynamischen Webseiten (Script-Programme) des User-Interface nur → UTF-8: In diesem Fall entfällt die Code-Umwandlung aller von den AnwenderInnen eingegebenen Daten.

→ UTF-8 bietet eine besonders sparsame Speicherform: Die weltweit meist-verwendeten ASCII-Zeichen brauchen (genauso wie im ASCII-Code) nur je 1 Byte Speicherplatz. Die deutschen (und die meisten anderen europäischen) Sonderzeichen ÄÖÜäöüß brauchen je 2 Byte, lediglich das €-Zeichen 3 Byte.

Collation
Die Anordnung der in einem ↑ Zeichensatz enthaltenen Zeichen ist willkürlich. Da ein Computer normalerweise nach Bitmuster sortiert, werden die gleichen Texte daher je nach Zeichensatz unterschiedlich sortiert. Darüber hinaus entwickelten sich auch bei Verwendung der gleichen Zeichen unterschiedliche nationale und lokale Sortier-Gewohnheiten und sogar -Standards. Diese Regeln ergeben eine Unter-Gliederung der jeweiligen Zeichensätze.

Datenbank-Collation

Bei Anlage einer neuen Datenbank ist außer ihrem Namen meist auch die Standard-Collation (Sortier-Regel) wählbar: Sie wird für alle Tabellen dieser Datenbank verwendet, wenn keine anderen Vereinbarungen getroffen werden.
♣ Tipp: Verwenden sie Regeln, die UTF-8 verwenden, und zwar je nach Bedarf und Möglichkeiten mit nationalen (utf8_german) oder internationalen (utf8_general, utf8_unicode) Sortier-Regeln.

Verzichten sie nicht auf diese Konfiguration, denn sonst wird die vor-eingestellte Collation verwendet - bei MySQL meist die in Schweden (Heimat der EntwicklerInnen) verwendete.

Tabellen-Collation

Bei Anlage einer neuen Tabelle ist außer ihrem Namen meist auch die Standard-Collation für alle Text-Felder dieser Tabelle wählbar.
♣ Tipp: Verwenden sie für Tabellen nach Möglichkeit die gleiche Standard-Collation wie für die Datenbank.

Feld-Collation

Bei Anlage eines Text-Feldes (Tabellen-Spalte zur Verwaltung von Text-Daten) kann man u.a. auch die individuelle Sortier-Ordnung (Collation) angeben.

Wenn man auf die Festlegung der Collation verzichtet, werden die Standard-Regeln der Tabelle verwendet, wenn auch diese fehlen, jene der Datenbank.

Wenn sie eine Collation wählen, deren Name mit ascii beginnt, dann können sie in dieses Feld nur ASCII-Zeichen eintragen, d.h. keine deutschen (oder andere) Sonderzeichen.
Meist kann man jede der hier angegebenen Sortier-Regeln auch nachträglich ändern. Diese Möglichkeit kann je nach Datenbank und Version auch gut versteckt sein..

♣ Tipp: Verwenden sie nach Möglichkeit für alle Tabellen und Text-Felder innerhalb einer Datenbank die gleichen Sortier-Regeln. Alles andere kostet viel Aufwand bei der Entwicklung, sowie Missverständnisse und Ärger bei der Anwendung.

Beispiele

Die Dokumentation zu diesem Thema ist leider spärlich.
Die hier vorgestellten Beispiele wurden empirisch (durch praktische Versuche) gewonnen und sind unverbindlich !
Bei Durchsicht der Internet-Foren stößt man auf wenig Information aber starke Emotionen. Hier wird nicht diskutiert, welche Sortierung 'besser' oder 'richtiger' ist: Im Zweifel entscheidet die betriebliche Praxis: Die AnwenderInnen sollten gesuchte Daten rasch und sicher finden...
_bin
Collations mit dieser Nachsilbe befolgen die Reihenfolge der ASCII-Zeichen. Die wichtigste Regel sortiert (Ziffern vor) Großbuchstaben vor Kleinbuchstaben, z.B.
A ... Z a ... z
Sonderzeichen folgen (wenn sie im betreffenden Zeichensatz enthalten sind) erst nach den ASCII-Zeichen in Unicode-Reihenfolge, z.B.
Argentinien ... Zypern ... Österreich
_general
Collations mit dieser Nachsilbe unterscheiden nicht zwischen großen und kleinen Buchstaben:
{A a} ... {z Z}
Strings, die sich nach Umwandlung in Großbuchstaben nicht unterscheiden, werden nicht sortiert und hier in {Klammern} dargestellt. Je nach Reihenfolge der Datensätze ist daher auch diese Sortierung möglich:
{a A} ... {Z z}
Sonderzeichen werden unterschiedlich behandelt.
latin1_bin
ergibt für deutsche Texte (immer ?) die gleiche Sortierung wie utf8_bin oder cp_1250_bin: Großbuchstaben vor Kleinbuchstaben, erst danach Umlaute, z.B.
A ... Z a ... z Ä ... ä
latin1_general_ci
macht keinen Unterschied zwischen großen und kleinen Zeichen, ebenso nicht zwischen Stammlauten und Umlauten:
{A a Ä ä} ... {z Z}
latin1_general_cs
Großbuchstaben vor Kleinbuchstaben. Führende Umlaute folgen erst nach den ASCII-Zeichen. Umlaute innerhalb von Worten werden genauso behandelt wie ihre Stammlaute:
München Murau Zürich Öblarn
latin1_german1
ergibt für deutsche Texte (immer ?) die gleiche Sortierung wie utf8_general oder cp_1250_general:
Diese Collation macht keinen Unterschied zwischen großen und kleinen Zeichen, ebenso nicht zwischen Stammlauten und Umlauten:
{A a Ä ä} ... {z Z}
latin1_german2
Großbuchstaben vor Kleinbuchstaben. Umlaute werden nach ihren Stammlauten sortiert, jedoch unmittelbar vor ihren Umschreibungen:
A Aa Ab Ac Ad Ä Ae
utf8_unicode
ergibt für deutsche Texte (immer ?) die gleiche Sortierung wie utf8_roman und erlaubt die Codierung der ersten 65000 Unicode-Zeichen. Diese Collation macht keinen Unterschied zwischen großen und kleinen Zeichen, ebenso nicht zwischen Stammlauten und Umlauten:
{A a Ä ä} ... {z Z}

Übersicht:

Zeichen in {Klammern} werden untereinander nicht sortiert, d.h. sie bleiben in der Reihenfolge der Datensätze (= in der Reihenfolge ihrer Erzeugung).

Je nach Zeichensatz kann man unterschiedliche und unterschiedlich viele Zeichen verwenden:
ASCII (keine Umlaute), CP1250 und Latin1 (deutsche Umlaute), Unicode/UTF8 (deutsche Umlaute + alle weltweit wichtigen Zeichen).
CollationSortierung
ascii_generalA a O o ss sz U u
cp1250_binA O U a o ss sz u Ä Ö Ü ß ä ö ü
cp1250_general{AaÄä} {OoÖö} ß ss sz {UuÜü}
latin1_binA O U a o ss sz u Ä Ö Ü ß ä ö ü
latin1_general_ci{AaÄä} {OoÖö} ss {sz ß}{UuÜü}
latin1_general_cs{AaÄä} {OoÖö} ss {sz ß}{UuÜü}
latin1_german1{AaÄä} {OoÖö} ß ss sz {UuÜü}
latin1_german2{AaÄä} {OoÖö} {ss ß} sz {UuÜü}
utf8_binA O U a o ss sz u Ä Ö Ü ß ä ö ü
utf8_general{AaÄä} {OoÖö} ß ss sz {UuÜü}
utf8_unicode{AaÄä} {OoÖö} {ss ß} sz {UuÜü}
Wikipedia: Sortierung (de), Collation (en)  

SQL Sortierung

SQL-Anweisung   select

Diese Anweisung gibt als einzige Daten zurück, nämlich die Ergebnis-Daten einer 'Suche' in allgemeinem Sinn.
Die Daten werden ohne besondere Anweisung nicht sortiert und erscheinen daher in der Reihenfolge der Erzeugung (der Datensätze), normalerweise identisch mit aufsteigender Sortierung nach dem Primär-Schlüssel.

SQL-Klausel   order by

Nur zusammen mit der select-Anweisung kann die optionale Klausel order by zur Sortierung verwendet werden.
Beispiel:
select * from personen order by pers_zuname;
Liefert alle Felder (*) der Tabelle personen, (aufsteigend) sortiert nach den Daten in Feld (Spalte) pers_zuname

Sortier-Folge:

Die Sortierung erfolgt bei Zahlen-Feldern nach dem Zahlenwert, bei Text-Feldern nach den für das jeweilige Feld (Spalte) konfigurierten ↑ Collation-Regeln.

Es ist möglich, temporär eine andere Collation zu verwenden: Fügen sie die Ergebnisse einer Suche in eine temporäre Tabelle ein und konfigurieren sie darin für das Sortier-Feld die gewünschte Collation. Nach der Sortierung wird die temporäre Tabelle gelöscht.

Sortier-Richtung

Ohne besondere Anweisung wird immer aufsteigend sortiert, ebenso wie mit dem nachfolgenden Attribut asc
Mit dem Attribut desc wird absteigend sortiert.
Beispiele:
select * from personen order by pers_zuname asc;
select * from personen order by pers_zuname desc;

Kombination

Wenn die Sortierung nach einem einzelnen Feld keine ausreichende Trennung ergibt, kann man weitere Felder angeben. Die Namen aller weiteren Sortier-Felder werden mit , Beistrich getrennt. Die Sortierung nach dem 2.Feld wird nur auf jene Datensätze angewendet, die im 1.Feld die gleichen Daten enthalten, usw.
Beispiel:
select * from personen order by pers_vorname, pers_zuname;

Unabhängige Sortierung

Die Sortierung erfolgt unabhängig von den zur Ausgabe bestellten Feldern. Daher ist auch die Sortierung nach Feldern möglich, die im Ergebnis nicht enthalten sind:
select pers_zuname from personen order by pers_vorname;
Liefert eine Liste von Zunamen, die jedoch nach Vornamen geordnet sind.

NULL, Leertext und Leerzeichen

● Jedes Feld kann den Wert NULL (unbestimmt) enthalten, wenn das in der Daten-Struktur zugelassen wird. Verwechseln sie NULL nicht mit der Zahl 0 oder mit einem leeren Text.
♣ Es wird empfohlen, für alle Felder, deren Daten von Menschen eingegeben werden, NULL zu erlauben. Das erspart nachfolgende Probleme, weil in der Praxis fast immer ungeplante oder unvollständige Daten auftreten.

● Ein Leertext ist ein gültiger Text, der als Sonderfall keine Zeichen enthält.

● Ein Text aus einem oder mehreren Leerzeichen ist ein unangenehmer und meist unerwünschter Sonderfall. Man kann nur aus der Anzahl der Zeichen (SQL-Funktion length() ) erkennen, dass ein Text enthalten ist.

● Führende Leerzeichen oder Tabulator-Zeichen können die Sortierung und manchmal auch die Suche empfindlich stören, z.B. ein Text wie ' xyz'
♣ Es wird empfohlen, jede Datenbank periodisch von solchen (meist nicht beabsichtigten) Daten zu säubern. Dazu kann man SQL-Anweisungen oder Programme (Perl, PHP, ...) verwenden.
In der Regel werden diese Sonderfälle so sortiert:
NULL, <blank> <leer> <blank>*
Die Codes bezeichnen:
<blank> ... 1 oder mehr Leerzeichen
<leer> ... LeerText ohne Zeichen
<blank>* Text mit einem führenden Leerzeichen
Diese Sortierung scheint unabhängig von der ↑ Collation zu sein. Das wurde jedoch empirisch ermittelt und ist nicht verlässlich !

SQL Gruppierung

SQL-Klausel   group by

Diese Klausel wird in Verbindung mit der Anweisung select zur Zusammenfassung von Gruppen verwendet.

Die Bedingung dafür ist, dass sich die (mit select) gesuchten Daten nach einem Gruppen-Kriterium sortieren lassen.

Das Ergebnis enthält ebenso viele Zeilen, wie die Anzahl unterschiedlicher Daten im gruppierten Feld.

Die Ausgabe sollte das Gruppierungs-Feld enthalten und zusätzlich mindestens eine der Aggregat-Funktionen (avg(), count(), min(), max(), sum() )
Beispiel:
Eine Tabelle personen wird nach einem Feld pers_sex in Gruppen zusammengefasst. Das Feld enthält entweder ein Zeichen M oder W oder NULL:
select pers_sex, count(*) from personen group by pers_sex;
Beispiel für ein Ergebnis dieser SQL-Anweisung:
pers_sexcount(*)
NULL15
M378
W412
Liefert die Anzahl von Datensätzen mit unbestimmtem Inhalt sowie von Männern und Frauen in der Tabelle.

Kombination

Man kann die Gruppierung staffeln, d.h. mehrere Gruppierungs-Felder kombinieren. Die Namen aller weiteren Gruppierungs-Felder werden mit , Beistrich getrennt.

Das Beispiel rechts gruppiert zuerst nach Geschlecht, dann nach Vornamen. Jeder Vorname liefert eine Ausgabe-Zeile.
Beispiel:
select pers_sex,pers_vorname,count(*) from personen group by pers_sex,pers_vorname;
Beispiel für ein Ergebnis:
pers_sexpers_vornamecount(*)
NULLNULL15
MAnton25
M...
MZacharias2
WAnna18
W...
WZara4
Zum Inhaltsverzeichnis Homepage von PS-Trainer   >   Entwicklung   >   SQL   >   Sortierung