Sortierung unterschiedlich, trotz ähnlichem Group By Statement

[GoatMachine]

Hallo,

Das Problem ist, dass die Sortierung auf einer korrekt dargestellt wird, auf einer anderen wiederum nicht, obwohl das Group By Statement ähnlich ist.

Seite 1:
Screen

SQL

SELECT group_concat(nachname, ', ', vorname , IF(Hrsg = 1, ' (Hrsg.)', '') ORDER BY nachname SEPARATOR '; ') AS autoren,
auflage_id, buch_id, titel , band, erscheinungsjahr
FROM(
SELECT auf.id AS auflage_id, b.id AS buch_id, ifnull(auf.titel, b.originaltitel) AS titel, a.nachname, a.vorname, ab.Hrsg, a.ID, 
IF(b.band = -1, b.band_str, b.band) AS band, auf.erscheinungsjahr
FROM
nutzer u,
sammlung s,
auflage auf,
buch b,
schriftstellerbuch ab,
schriftsteller a
WHERE u.id = 2
AND u.id = s.userid
AND s.buchid= auf.id
AND auf.buecher_id = b.id
AND b.id = ab.buecher_id
AND ab.autor_id = a.id) AS Temp
GROUP BY buch_id, auflage_id, titel , band, erscheinungsjahr
ORDER BY autoren, CONVERT(band, DECIMAL) ASC, erscheinungsjahr ASC

Seite 2:
Screen

SQL

SELECT group_concat(nachname, ', ', vorname , IF(Hrsg = 1, ' (Hrsg.)', '') ORDER BY nachname SEPARATOR '; ') AS autoren, 
reihe.Name, buch.ID, typ.ID AS BuchArtId, typ.Art, auflage.Thumb, buch.originaltitel, 
group_concat(DISTINCT bs.Sprache ORDER BY bs.Sprache SEPARATOR '/') AS LANGUAGE, IF(buch.band = -1, buch.band_str, buch.band) AS band, 
gebiet.unterkategorie AS Genre2, kategorie.hk_genre AS Genre1                        
FROM buch, auflage, gebiet, kategorie, sprache AS bs, buchsprache, reihe, typ, schriftsteller, schriftstellerbuch                        
WHERE buch.reihen_id = 678                        
AND reihe.ID = buch.reihen_id                        
AND auflage.Buecher_ID = buch.ID                        
AND buch.Genre_ID = gebiet.ID                        
AND gebiet.hauptkategorie = kategorie.ID                        
AND buchsprache.Buecher_ID = buch.ID                        
AND buchsprache.Sprachen_ID = bs.ID                        
AND typ.ID = buch.typ_id                        
AND schriftstellerbuch.buecher_ID = buch.ID                        
AND schriftsteller.ID = schriftstellerbuch.autor_ID                        
GROUP BY buch.ID                        
ORDER BY BuchArtId,  CONVERT(band, DECIMAL), auflage.erscheinungsjahr

Wie man wahrscheinlich erkennt, will ich die Sortierung auf Seite 1, genauso haben, wie sie auf Seite 2 aussieht.

Hat einer eine Idee, woran das liegen könnte?

Gruß

 

[akretschmer]

Hallo,

Das Problem ist, dass die Sortierung auf einer korrekt dargestellt wird, auf einer anderen wiederum nicht, obwohl das Group By Statement ähnlich ist.

Hat einer eine Idee, woran das liegen könnte?

Gruß

Nein. Wie soll man das erkennen ohne z.B. Deine Tabellen zu kennen und auch ohne zu wissen, wie die Zuordnung Deiner Felder zu der Bilderausgabe da erfolgt.

Aber vielleicht enthält Dein Subject schon einen Hinweis: das GROUP BY hat exakt nix mit der Sortierung zu tun. Dazu kommt, daß Du einen massiven Verstoß gegen eine Regel der SQL-Syntax hast: in Abfragen mit Aggregatsfunktionen (hier: group_concat) müssen alle Spalten des Resultats ENTWEDER durch eine Aggregatsfunktion gebildet werden ODER im GROUP BY später auftauchen. Dies ist bei Dir nicht der Fall. Die gute Nachricht für Dich: MySQL ist wohl die einzigste Datenbank, die das akzeptiert und da wahllos irgend etwas macht. Die schlechte Nachricht: wohl nicht das, was Du erwartest.

Wenn Du Deine Abfragen jetzt umbaust: verwende besser expliziete JOINs, ist besser verständlich und trennt die JOIN-Definitionen und die WHERE-Conditions.

Andreas

 

[GoatMachine]

Dazu kommt, daß Du einen massiven Verstoß gegen eine Regel der SQL-Syntax hast: in Abfragen mit Aggregatsfunktionen (hier: group_concat) müssen alle Spalten des Resultats ENTWEDER durch eine Aggregatsfunktion gebildet werden ODER im GROUP BY später auftauchen. Dies ist bei Dir nicht der Fall. Die gute Nachricht für Dich: MySQL ist wohl die einzigste Datenbank, die das akzeptiert und da wahllos irgend etwas macht. Die schlechte Nachricht: wohl nicht das, was Du erwartest.

Also ich erkenne keinen Verstoß beim SQL, der nicht funktioniert. Wo die Ausgabe so aussieht, wie sie soll, da sind die Verstöße. Oder habe ich was übersehen?

Oder müssen auch die Spalten wie "vorname, nachname" in den Group By?

 

[akretschmer]

Also ich erkenne keinen Verstoß beim SQL, der nicht funktioniert. Wo die Ausgabe so aussieht, wie sie soll, da sind die Verstöße. Oder habe ich was übersehen?

Oder müssen auch die Spalten wie "vorname, nachname" in den Group By?

Exakt. Alle Spalten entweder in einer Aggregatsfunktion oder im group by genannt. Man überlegt wohl im Core-Team von PG, daß etwas aufzuweichen, aber dann müssen das Spalten sein, wo ein Primary key oder Unique Index drauf sind, dann ist es eh eindeutig. Aber das prüft MySQL an der Stelle nicht.

Demo MySQL:

mysql> select * from doofes_beispiel;
+-------+------+
| name  | zeit |
+-------+------+
| max   |   10 |
| peter |   20 |
| max   |   30 |
+-------+------+
3 rows in set (0.06 sec)

mysql> select name, sum(zeit) from doofes_beispiel;
+------+-----------+
| name | sum(zeit) |
+------+-----------+
| max  |        60 |
+------+-----------+
1 row in set (0.06 sec)

Ist es das, was Du erwartet hast? Ist es richtig?

Demo PostgreSQL:

test=*# select * from doofes_beispiel ;
 name  | zeit
-------+------
 max   |   10
 peter |   20
 max   |   30
(3 rows)

Time: 0,151 ms
test=*# select name, sum(zeit) from doofes_beispiel ;
ERROR:  column "doofes_beispiel.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name, sum(zeit) from doofes_beispiel ;
               ^
Time: 0,206 ms
test=*# select name, sum(zeit) from doofes_beispiel group by name;
 name  | sum
-------+-----
 peter |  20
 max   |  40
(2 rows)

 

[GoatMachine]

Einen kleinen Ausschnitt vom Datenbankmodell habe ich hier, allerdings sind die Spaltennamen etwas anders, als in den SQL Befehlen. Hoffe das stört nicht weiter:

Ich denke, das Problem hat irgendwas damit zu tun, das der Inhalt, der Spalte kein INT ist.

Überall, wo kein anderes Zeichen als reine Zahlenwerte stehen, funktioniert es, ohne Probleme. Allerdings bringt es auch nichts, wie ich das mal zum testen versucht habe, den '/' herauszunehmen... Wenn ich allerdings Band komplett aus dem ORDER nehme, dann funktioniert es...So kann ich es allerdings nicht lassen, da es jetzt nur nach dem Erscheinungsjahr sortiert wird und das nicht unbedingt mit der Reihenfolge, von der Reihe übereinstimmt

 

[akretschmer]

Einen kleinen Ausschnitt vom Datenbankmodell habe ich hier, allerdings sind die Spaltennamen etwas anders, als in den SQL Befehlen. Hoffe das stört nicht weiter:

Ich denke, das Problem hat irgendwas damit zu tun, das der Inhalt, der Spalte kein INT ist.

Überall, wo kein anderes Zeichen als reine Zahlenwerte stehen, funktioniert es, ohne Probleme. Allerdings bringt es auch nichts, wie ich das mal zum testen versucht habe, den '/' herauszunehmen... Wenn ich allerdings Band komplett aus dem ORDER nehme, dann funktioniert es...So kann ich es allerdings nicht lassen, da es jetzt nur nach dem Erscheinungsjahr sortiert wird und das nicht unbedingt mit der Reihenfolge, von der Reihe übereinstimmt

Ich weiß nicht, um welche Spalte es geht, aber man sollte immer die richtigen Datentypen nehmen. Zahlen werden nun mal anders sortiert als Texte. Vielleicht kannst Du da mit Ersetzen von Zeichen und casten nach INT oder so was erreichen, besser wäre, von Anfang an sauber zu arbeiten.

 

[akretschmer]

Ich weiß nicht, um welche Spalte es geht, aber man sollte immer die richtigen Datentypen nehmen.

Um auch das noch mal zu zeigen:

mysql> create table blub (value text);
Query OK, 0 rows affected (0.06 sec)  

mysql> insert into blub values (20);
Query OK, 1 row affected (0.06 sec) 

mysql> insert into blub values ('20 tausend');
Query OK, 1 row affected (0.06 sec)

mysql> select sum(value) from blub;
+------------+
| sum(value) |
+------------+
|         40 |
+------------+
1 row in set (0.06 sec)

richtig rechnen kann es also damit nicht ;-)

mysql> insert into blub values ('10 tausend');
Query OK, 1 row affected (0.06 sec)

mysql> select * from blub order by value;
+------------+
| value      |
+------------+
| 10 tausend |
| 20         |
| 20 tausend |
+------------+
3 rows in set (0.06 sec)

sortieren klappt auch nicht ;-)

Andreas

 

[GoatMachine]

Oh sorry, nicht dabei geschrieben. Ich meine die Spalte "Band".

Das Problem ist, das dies sowohl Zahlenwerte, als auch Zeichen enthalten kann.
Geht man z.B. von Sammelausgaben aus "1 - 3" <- Das '-'. Oder wie in meinem Beispiel oben "01/2008" -> '/'.

Ich konvertiere diese Spalte beim sortieren ja nach DECIMAL. Das Problem dabei ist nur, das nur die Zahlen vor dem '/' bestehen bleiben. Darum ist die Sortierung, nachdem SQL sogar richtig, aber nicht die, die ich wünsche

 

[akretschmer]

Oh sorry, nicht dabei geschrieben. Ich meine die Spalte "Band".

Das Problem ist, das dies sowohl Zahlenwerte, als auch Zeichen enthalten kann.
Geht man z.B. von Sammelausgaben aus "1 - 3" <- Das '-'. Oder wie in meinem Beispiel oben "01/2008" -> '/'.

Ich konvertiere diese Spalte beim sortieren ja nach DECIMAL. Das Problem dabei ist nur, das nur die Zahlen vor dem '/' bestehen bleiben. Darum ist die Sortierung, nachdem SQL sogar richtig, aber nicht die, die ich wünsche

Dann bist Du ja nun auf der Zielgeraden, Du mußt nur noch passend definieren, wie sortiert werden soll. Ist '1 - 3' nun größer oder kleiner als '01/2008'?

 

[GoatMachine]

Naja ok, das waren jetzt Beispiele, warum ich nicht nur INT nutzen kann

Zahlen werden auch richtig sortiert, da ich das ja konvertiere. Das Problem ist jetzt halt nur, das alles nachdem Zeichen, welches keine Zahl ist, abgeschnitten wird.

01/2008 => 1
02/2008 => 2
03/2008 => 3
05/2007 => 5
06/2007 => 6
06/2013 => 6
07/2007 => 7
08/2007 => 8
09/2007 => 9
10/2007 => 10
11/2007 => 11
12/2007 => 12

Darum ist die Sortierung so gesehen ja korrekt. Und als ich zum Test '/' ersetzt habe mit einem '', kam:

01/2008 => 12008
02/2008 => 22008
03/2008 => 32008
05/2007 => 52007
06/2007 => 62007
06/2013 => 62013
07/2007 => 72007
08/2007 => 82007
09/2007 => 92007
10/2007 => 102007
11/2007 => 112007
12/2007 => 122007

Ein weiteres Problem ist ja, das es allgemein gültig sein muss. Es gibt auch andere Einträge wo nicht unbedingt ein '/' vorkommt, wo dann ganze Wörter stehen, die auch falsch sortiert werden. Wenn am Anfang der Spalte direkt ein Buchstabe bzw. ein Zeichen steht, wird der Wert direkt zur 0. Kein Wunder das die Ausgabe nicht passt.

 

[akretschmer]

Naja ok, das waren jetzt Beispiele, warum ich nicht nur INT nutzen kann

Zahlen werden auch richtig sortiert, da ich das ja konvertiere. Das Problem ist jetzt halt nur, das alles nachdem Zeichen, welches keine Zahl ist, abgeschnitten wird.

test=*# select * from date_order order by 1;
    d
---------
01/2007
01/2008
02/2007
06/2013
10/2000
12/2013
(6 rows)
 
Time: 0,280 ms
test=*# select * from date_order order by substring(d,4,4), substring(d,1,2);
    d
---------
10/2000
01/2007
02/2007
01/2008
06/2013
12/2013
(6 rows)

Ein weiteres Problem ist ja, das es allgemein gültig sein muss. Es gibt auch andere Einträge wo nicht unbedingt ein '/' vorkommt, wo dann ganze Wörter stehen, die auch falsch sortiert werden. Wenn am Anfang der Spalte direkt ein Buchstabe bzw. ein Zeichen steht, wird der Wert direkt zur 0. Kein Wunder das die Ausgabe nicht passt.

Exakt. Finde einen Weg, das zu beschreiben, wie es *richtig* sein soll. Dann kann man das auch in eine Regel fassen. Aber zuerst mußt Du halt diese Regel definieren. Im Beispiel oben soll halt zuerst nach dem Jahr und dann nach dem Monat sortiert werden, das kann man dann auch umsetzen.

 

[GoatMachine]

Exakt. Finde einen Weg, das zu beschreiben, wie es *richtig* sein soll. Dann kann man das auch in eine Regel fassen. Aber zuerst mußt Du halt diese Regel definieren. Im Beispiel oben soll halt zuerst nach dem Jahr und dann nach dem Monat sortiert werden, das kann man dann auch umsetzen.

Da ist das Problem, das es so extrem unterschiedlich ist.

Einmal wie oben, Monat/Jahr, wo erst nachdem Jahr, dann nachdem Monat sortiert werden soll. Dann gibt es Einträge wie "Spezial 1", "Spezial 2", in denen das Wort Spezial wieder alles umwirft. Dort soll ganz normal Sortiert werden, wie ich es eben aufgeführt habe. Und dann wie vorhin genannt "1 - 3".
Ich weiß nicht, wie ich da eine Regel definieren kann, da es so viele unterschiedliche Einträge gibt.

 

[akretschmer]

Ich weiß nicht, wie ich da eine Regel definieren kann, da es so viele unterschiedliche Einträge gibt.

Da kann Dir aber keiner mehr helfen...

 

[GoatMachine]

Ja, mir kam schon eine Idee wie ich das umsetzen könnte...muss halt vom User selber gemacht werden, durch vorgaben von mir....gucken wie ich das lösen kann.

Vielen dank, das du mir geholfen hast.


Das größte Problem ist eher, das jeder Datensatz eine andere ORDER BY Klausel hat...

 

[akretschmer]

Das größte Problem ist eher, das jeder Datensatz eine andere ORDER BY Klausel hat...

Jeder Datensatz? Sortieren kann man Einzelstücke nicht wirklich, nur Mengen.