sql: Sortierung

[Lukas.]

Hi,

... ORDER BY Artikelnummer ASC ergibt:

ABC-200411206

ABC-31012896

weil Artikel1 mit einer 2 hinter dem "ABC-" beginnt?

Wie bringe ich sql bei, dass die längere Zahl hier den Ausschlag für die Sortierung gibt?

Lukas

[TS]

Hallo und guten Morgen,

... ORDER BY Artikelnummer ASC ergibt:

ABC-200411206

ABC-31012896

weil Artikel1 mit einer 2 hinter dem "ABC-" beginnt?

Wie bringe ich sql bei, dass die längere Zahl hier den Ausschlag für die Sortierung gibt?

Normalisieren und Warengruppe (oder was das ABC bedeuten soll) und Artikelnummer in zwei eigenständige Spalten schreiben. WGR als Char und Artikelnummer als Decimal (ohne Kommastellen) oder INTEGER und dann einen Index über beide Spalten anlegen.

Oder wenn das DBMS das hergibt, einen berechneten Index über die gemischte Spalte anlegen.

MySQL kann sowas nicht. ## weiß nicht wie ;-O

Korrektur:
http://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
Das müsste sowas sein...

Grüße
TS

[Matthias Apsel]

Hallo Lukas.,

Wie bringe ich sql bei, dass die längere Zahl hier den Ausschlag für die Sortierung gibt?

Ungetestet:

SELECT 
  LEN(w_nr) as laenge, 
  w_nr 
FROM `table_name`
ORDER BY 
  laenge ASC, w_nr DESC

Bis demnächst
Matthias

[dedlfix]

Tach!

Wie bringe ich sql bei, dass die längere Zahl hier den Ausschlag für die Sortierung gibt?

Das Prinzip nennt sich Natural Sort, das macht zum Beispiel Windows im Dateisystem, aber MySQL hat das nicht als Leistungsmerkmal.

SELECT 
  LEN(w_nr) as laenge, 
  w_nr 
FROM `table_name`
ORDER BY 
  laenge ASC, w_nr DESC

Ja, das wäre eine einfache Methode, unter der Annahme, dass der Prefix immer gleich lang ist. Man muss die Längenermittlung übrigens auch nicht als Feld in der SELECT-Klausel aufführen, es reicht, LEN(w_nr) in der ORDER-BY-Klausel zu haben.

Wenn der Prefix unterschiedlich lang sein kann, muss man sich was überlegen, wie man den entfernen kann. Zum Beispiel einen Substring nach dem Bindestrich ermitteln. MySQL hat ja auch Stringfunktionen an Bord.

dedlfix.

[Lukas.]

Hallo zusammen,

Wenn der Prefix unterschiedlich lang sein kann, muss man sich was überlegen, wie man den entfernen kann. Zum Beispiel einen Substring nach dem Bindestrich ermitteln. MySQL hat ja auch Stringfunktionen an Bord.

Das dachte ich mir auch, aber folgende Query bringt folgendes Ergebnis. Warum? (ABC ist übrigens das Lieferantenkürzel, womit ansich die nachfolgende Sortierung ein bischen doppelt gemoppelt ist, was aber nichts zur Sache tut)

... ORDER BY 
Lieferant,
SUBSTRING_INDEX( Artikelnummer, '-', 1 ),
SUBSTRING_INDEX(SUBSTRING_INDEX(Artikelnummer, '-', 2), '-', -1)

Ergebnis:

ABC-200412006
ABC-31007896
ABC-31007898

Das dürfte doch nicht, oder?

Lukas

[dedlfix]

Tach!

Das dachte ich mir auch, aber folgende Query bringt folgendes Ergebnis. Warum?

Die Substring-Ermittlung hat als Ergebnis immer noch Strings und keine Zahlenwerte. Die Substringermittlung war nur der erste Schritt, um den Zahlenanteil zu ermitteln. Jetzt muss weiterhin die Länge als erstes Kriterium herangezogen werden. Oder du machst einen Typecast in einen echten Zahlenwert.

Zu beachten wäre da noch, wenn die Nummern führende Nullen haben können. Die müsste man wegtrimmen, wenn man Strings vergleicht. Beim Typecast verschwinden die ja von selbst.

dedlfix.

[Lukas.]

Hi,

na klar, sind noch Strings. Hatte ich vergessen.

Zu beachten wäre da noch, wenn die Nummern führende Nullen haben können. Die müsste man wegtrimmen, wenn man Strings vergleicht. Beim Typecast verschwinden die ja von selbst.

Typecast kann ich vergessen, es gibt auch Artikelnummern mit Buchstaben drinne.

Lukas

[TS]

Hallo und guten Tag,

na klar, sind noch Strings. Hatte ich vergessen.

Zu beachten wäre da noch, wenn die Nummern führende Nullen haben können. Die müsste man wegtrimmen, wenn man Strings vergleicht. Beim Typecast verschwinden die ja von selbst.

Typecast kann ich vergessen, es gibt auch Artikelnummern mit Buchstaben drinne.

Dann könntest Du für die Schmuddelpraxis den Weg anders herum gehen und den hinteren Anteil einfach vorne mit neutralen Elementen (bezüglich der Sortierung, also z.B. Leerzeichen oder 0) auffüllen, bis eine (maximale) Gesamtlänge von X Zeichen errreicht ist.

Das ist aber Schmuddelei, weil die Sortierung dann immer noch über eine temporäre (Index-)Tabelle geht, die das DBMS bei jedem Select erstellen muss.

Der richtige Weg wäre mMn die passende Atomisierung der Daten.

Der zweitbeste Weg wären zwei Trigger für Insert und Update, die eine berechtnete Spalte füllen, auf der dann ein Index liegt. Dann kann das DBMS beim ORDER BY diesen Index benutzen, der aber nicht bei jedem Select neu aufgebaut werden muss.

Grüße
TS

[Lukas.]

Hi TS,

Der richtige Weg wäre mMn die passende Atomisierung der Daten.

Das ist nahezu unmöglich, weil ich nie wissen kann, welch tolle Ideen die Lieferanten meiner Kunden so haben. Die kommen auf die genialsten Ideen bei der Vergabe der Artikelnummern ihrer Artikel.

Das Gute ist, dass die Sortierung nicht das Wichtigste ist, sondern sie nur im Großen und ganzen stimmen muß. Zudem gibts bei mir ja auch noch eine Suchfunktion, um das ganze "wasserdicht" zu machen.

L.

[Rolf b]

Hallo Lukas,

wenn Du Lieferanten hast, deren Artikelnummern nicht rein numerisch sind, wie stellst Du Dir dann eine Sortierung nach numerischer Reihenfolge auch nur ansatzweise vor? Ein Lieferant hat numerische Artikelnummern, die sortierst Du numerisch, der andere hat mixed, die sortierst Du alphabetisch? Das macht die Sache SEHR schwierig.

Du bist bisher nicht auf den Vorschlag eingegangen, das Problem durch Splitten der Artikelnummer Column in VARCHAR-Columns Lieferantenkürzel und LieferantenArtikelnummer zu lösen. Magst Du uns erklären, warum Du das nicht willst? Es ist deutlich einfacher (und auch fixer), bei Bedarf

SELECT CONCAT(Lieferantenkuerzel, '-', LieferantArtikelnummer) AS Artikelnummer, ...

zu schreiben und die Felder beim Sortieren nicht zurückgewinnen zu müssen.

Deine bisherigen Splitversuche zeigen dazu noch, dass Du weitere Anomalien zu befürchten hast. Was ist denn, wenn der Lieferant Dir eine Artikelnummer mit "-" drin schickt? In dem Moment holst Du Dir nur den Teil vor dem Lieferanten-Bindestrich. Wenn du ans DB Modell nicht heran kannst oder willst, kannst Du dann ein Extrafeld in der Query aufnehmen?

So:

SELECT Lieferant, Artikelnummer, Dings, Bums, Foo, LOCATE('-', Artikelnummer) AS strichpos
FROM tabelle
WHERE ...
ORDER BY Lieferant, LEFT(Artikelnummer, strichpos-1), SUBSTR(Artikelnummer, strichpos+1)

Damit geht's zuverlässig und vermutlich deutlich fixer als mit SUBSTRING_INDEX.

Die "richtige" Sortierung der Lieferanten-Artikelnummern bekommst Du so natürlich auch noch nicht, und es ist auch nicht zielführend, numerisch zu sortieren wenn Du Lieferanten mit alphanumerischen Artikelnummern hast. Statt dessen solltest Du die ArtNr rechtsbündig ausrichten, Du musst Dir dann nur eine maximale Länge festlegen. Mal angenommen, das wäre 10, dann geht's so:

SELECT Lieferant, Artikelnummer, Dings, Bums, Foo, LOCATE('-', Artikelnummer) AS strichpos
FROM tabelle
WHERE ...
ORDER BY Lieferant, LEFT(Artikelnummer, strichpos-1), LPAD(SUBSTR(Artikelnummer, strichpos+1), 10, '0')

Rolf

[Lukas.]

Hi Matthias,

LEN(w_nr) as laenge,

Dir auch nen Dank' und für alle Freunde von mysql: LEN = LENGHT

L.

[TS]

Hallo und guten Morgen,

... ORDER BY Artikelnummer ASC ergibt:

ABC-200411206

ABC-31012896

weil Artikel1 mit einer 2 hinter dem "ABC-" beginnt?

Noch ein Nachtrag:
Wenn Du solche Fragen postest, solltest Du z. B. das Datenbanksystem und das Create-Statement der betroffenen Tabelle auch zeigen, oder zumindest bekanntgeben welche Spaltentypen die Spalten haben und welche Indexe angelegt sind.

Grüße
TS