[Erledigt] ORDER BY im SUB SELECT bei MSSQL Server bringt Fehlermeldung

eagle275 · 18.10.2011, 09:46

ich bin gerade dabei meine mysql Datenbank (Firmeninterne Anwendung) auf MSSQL-Server zu migrieren. Hauptgrund ist die einfache Verschlüsselung auf Datenbank-Ebene (per Unternehmensrichtlinie vorgeschrieben).

Die normalen Abfragen, Spaltentypen und Unicode-Probleme konnte ich lösen.... Nun hänge ich an einer etwas komplizierter gestrickten Abfrage.

In meiner Anwendung gibts ein kleines Ticketsystem, über das einerseits das System selbst nach Konsistensprüfungen bestimmte Aufgaben verteilt (einerseits an die Admins, um Einstellungen von Systemen zu ändern, andererseits an die User, falls offensichtliche Fehler gemacht wurden).

Dieses Ticketsystem besteht im Grunde aus 3 Tabellen - eine Hilfstabelle, das die festgelegte Auswahl an Gründen enthält, wegen denen Tickets erstellt wurden (nötig, weil die verschiedenen Tickets unterschiedlich behandelt werden müssen) - dann die Tabelle über die eigentlichen Tickets und eine Tabelle, die den Verlauf der Ticketbearbeitung enthält (es ist bei mindestens einem Ticketgrund möglich, über das Ticketsystem Nachrichten zwischen Usern verschiedener Niederlassungen auszutauschen - diese Nachrichten müssen ALLE in ihrer zeitlichen Reihenfolge vorgehalten werden.

Das Problem ist die Abfrage, die alle fraglichen offenen Tickets zu einer Userklasse (festgelegt durch Niederlassung + Rechtestufe) auflisten soll. Um die "richtigen" Tickets zu erwischen, ist es erforderlich auch die VerlaufsTabelle mit zu beachten, weil Tickets, die eine Niederlassung an eine andere sendet, sind zwar offen, sind aber momentan der anderen Niederlassung zugeordnet (obwohl die erstere Niederlassung logischerweise auch drin steht). Ich muss also jeweils das allerneueste Verlaufsticket zu einem Ticket beachten. Dazu habe ich mir in mySQL eine etwas längliche Abfrage erstellt, die aber trotz der Verwendung einer SubQuery recht performant läuft.
Nun zum Problem .. der MSSQL-Server begrüßt meine Abfrage mit einer Fehlermeldung, zu der ich so recht keine Abhilfe weiß :
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

die zugehörige Abfrage :

Code:

SELECT * FROM 
  ( 
  SELECT verlauf.* , tickets.customer_id as customer_id,verlauf.ticket_id AS ticketnummer,
    tickets.abgeschlossen as abgeschlossen, tickets.anhang as anhang, tickets.ticket_id AS thema 
  FROM 
    ( SELECT * FROM ticketverlauf ORDER BY id DESC ) AS verlauf 
  LEFT JOIN 
     tickets ON tickets.id = verlauf.ticket_id 
  WHERE tickets.erledigt = 'nein' AND 1 =1 
  GROUP BY verlauf.ticket_id ORDER BY verlauf.ticket_id ASC 
  ) AS ticketanzeige 
WHERE 
  ( flag=1 OR flag=28 or flag=2 ) AND 1=1

bitte nicht wundern über das 1=1 am Ende, die Abfrage wird teils automatisch zusammengesetzt, da ist dieser Zusatz immer recht hilfreich (gewesen) - die verschiedenen flags dienen der Filterung der tickets für meine Berechtigungsstufe (als Beispiel) - auf Wunsch kann ich auch noch die Create-Table Anweisungen liefern

Wie löse ich nun das Problem, dass er mein ORDER BY im Inneren nicht mag, das aber die wichtige Sortierung der Verlaufstickets erledigt- zumindest in mysql funktioniert es NICHT, wenn ich das ORDER BY erst außen herum mit erledige (dann nimmt er automatisch immer das erste Verlaufsticket zu jedem Ticket und listet mir Tickets als offen auf, die eigentlich momentan an eine andere Niederlassung versendet wurden und dort erstmal bearbeitet werden müssen

ich hab dazu schon im Web bei Microsoft die Doku gelesen und auch im MS-Forum gesucht, aber keine rechte Abhilfe gefunden und momentan fehlt mir ehrlichgesagt die Fantasie, mir die Abfrage anders herum zu bauen - ohne auf Schleifen zu verfallen, die wohl der Performance abträglich wären (erst alle offenen Tickets auswählen, dann jeweils die zugehörigen Verlauftickets einlesen und prüfen, ob das Ticket überhaupt für den jeweiligen User "offen" ist .... ich hätte es schon gern wie bisher per JOINs in einer Abfrage erledigt - andererseits kann ich ja nicht in einem Statement sagen "wähle alle Verlaufstickets, gruppiert nach dem Ticket, sortiert nach Reihenfolge - und dann joine den Rest dazu "

Jens Clasen · 18.10.2011, 10:18

1) Deine Query benötigt das Group by da nicht. Mangels erkennbarer funktionaler Abhängigkeit der selektierten Spalten, dürfte das sogar für einen Fehler sorgen.

2) Wozu überhaupt die äußere innere Abfrage, wenn Du dann eh nur ein SELECT * FROM außen drum baust?

3) Warum das ORDER BY in ( SELECT * FROM ticketverlauf ORDER BY id DESC )? Die Ordnung macht an der Stelle doch überhaupt keinen Unterschied? Warum überhaupt das Subselect an der Stelle?

4)

Zitat:

andererseits kann ich ja nicht in einem Statement sagen "wähle alle Verlaufstickets, gruppiert nach dem Ticket, sortiert nach Reihenfolge - und dann joine den Rest dazu "

Das zwar nicht - Du könntest aber die Ergebnismenge bilden und nachträglich richtig sortieren. Das Ergebnis wird das selbe sein.

5) Zum Thema "offene Tickets anhand Verlaufstabelle": Das geht mittels eines Selfjoins durchaus.

Gruß Jens

eagle275 · 18.10.2011, 10:39

hm - ich dachte, genau die Antwort auf deine ersten Fragen stehe schon im Text...

speziell 3) -> es wird zuerst ein Ticket-Eintrag geschrieben. Der korrespondiert mit EINEM ticketverlauf Eintrag . Wenn dann ein User das Ticket bearbeitet, wird ein 2ter ticketverlauf-Eintrag erstellt (und bei mindestens einem Ticket"Grund" können es auch viel mehr Nachrichten sein, die im Ticketverlauf quasi hin und her gesendet werden.

An jedem Ticketverlauf-Eintrag steht im Feld flag, für welche User-Klasse es bestimmt ist - ich möchte aber nur die Tickets haben, die für die Abfragende User(Klasse) jetzt anstehen. Ohne das order by dort, nimmt die Datenbank den zuerst gefunden ticketverlauf-Eintrag, der zum Ticket passt. Ich seh aber erst am allerletzten Eintrag, ob das Ticket für den abfragenden User(-Klasse) bestimmt ist, oder nicht ...

das group by ist aus dem gleichen Grund da, ich brauch jedes Ticket nur einmal ....

ok, danke jens - mit deinen Hinweisen scheint es mir gelungen zu sein, die Abfrage entsprechend zu vereinfachen

aber ich probier mal kurz was aus und melde mich dann nochmal

Jens Clasen · 18.10.2011, 10:52

Okay - das heißt also, in MySQL missbrauchst Du das kapputte Group by. MySQL ignoriert die Notwendigkeit der funktionalen Abhängigkeit und gibt einfach den erstbesten Wert aus. Um den erstbesten Wert auch zu kriegen, verwendest Du ein Group by. Das das funktioniert ist mehr Zufall, als alles andere.

Was Du versuchen solltest ist, nur den letzten Verlaufsstatus des Tickets zu betrachten. Das kannst Du auf zwei Arten erreichen:

Code:

SELECT  
  v1.whatever
FROM 
  ticketverlauf v1 
    LEFT JOIN ticketverlauf v2
      ON v1.ticket_id=v2.ticket_id
        AND v2.zeitpunkt>v1.zeitpunkt
WHERE
  v2.id IS NULL

oder so

Code:

SELECT  
  v1.whatever
FROM 
  ticketverlauf v1 
    INNER JOIN (SELECT vi.ticket_id, MAX(vi.zeitpunkt) AS dt FROM ticketverlauf vi GROUP BY vi.ticket_id) v2
      ON v1.ticket_id=v2.ticket_id
        AND v1.zeitpunkt=v2.dt

Auf beide Ergebnisse kannst Du problemlos alle anderen relevanten Daten drauf joinen. Besser wäre sogar, wenn Du von der Ticket-Tabelle ausgehend joinst.

Gruß Jens

eagle275 · 18.10.2011, 12:21

ja, habe das jetzt ausgehend von der ticket tabelle gelöst .... und der einfache Join läuft natürlich noch schneller, als der subselect vorher

Jens Clasen · 18.10.2011, 12:23

Kannst Du das fertige Select mal bitte posten?

Gruß Jens

eagle275 · 18.10.2011, 12:26

Code:

SELECT 
  *,tickets.id as ticketnummer, tickets.ticket_id as thema 
FROM 
  tickets 
LEFT JOIN 
  ticketverlauf 
ON 
  tickets.id=ticketverlauf.ticket_id 
WHERE 
  erledigt='nein' AND ( flag=1 OR flag=28 or flag=2 ) AND 1=1 
ORDER By 
  tickets.id ASC, 
  ticketverlauf.id DESC

ich werd die Abfrage mal im Auge behalten, ob ich wirklich diejenigen Tickets erhalte, die ich haben will

Jens Clasen · 18.10.2011, 12:28

Irgendwie passt das nicht zu Deiner Beschreibung oben. Ich denke, im Verlauf hast Du zu jedem Ticket mehrere Stati stehen?

Gruß Jens

eagle275 · 18.10.2011, 12:38

nicht zwingend .... anfangs wird zu jedem ticket genau 1 verlaufs-Eintrag erzeugt. wenn der nun bearbeitet wird, dann kommt ein Verlaufseintrag hinzu

Jens Clasen · 18.10.2011, 12:53

Und genau das ist das Problem bei Deiner Query. Nimm an, Deine Niederlassung ist für den ersten Verlaufsstatus zuständig, nicht aber für den zweiten. Deine Query würde in diesem Fall den ersten zurück geben und den zweiten unterschlagen.

Gruß Jens

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] PHP keine Verbindung zu MSSQL Server 2008	TheSpion	PHP Einsteiger	13	29.09.2011 08:44
[Erledigt] MySQL Select ORDER BY - Inhalt einer Spalte höher bewerten	Mert	Datenbanken	10	03.03.2011 14:29
mssql: unable to connect to server	2wuck	Datenbanken	2	26.02.2011 20:51
Root Server Email	The Punisher	Server, Hosting und Workstations	15	30.11.2010 21:02
VisualSVN Server und PHP5	Frederic_S	Server, Hosting und Workstations	12	18.11.2010 17:50
Komme nicht zu MSSQL Server	Hehenberger	PHP Tipps 2010	6	04.11.2010 14:41
[Erledigt] phpMailer PHP4: SMTP Fehlermeldung	explode	PHP Tipps 2010	0	21.07.2010 12:58
Apache2.2 & PHP5 mit MSSQL Extension Fehler	AwaKe	Server, Hosting und Workstations	4	14.07.2010 14:36
[Erledigt] Intertabellarische Berechnungen, wie?	dave303	Datenbanken	13	05.03.2010 11:58
Ausgabe erfolgt nicht	Extremefall	PHP Tipps 2009	17	17.12.2009 19:51
Suche Tipps für Persormance-Steigerung (Geld für Nützliches)		Beitragsarchiv	18	16.08.2005 10:57
[Erledigt] Between-Query an MSSQL 2000 Server		PHP-Fortgeschrittene	0	30.06.2005 08:50
Fehlermeldung auf Server		Datenbanken	4	28.02.2005 12:00
MSSQL Server und PHP		PHP-Fortgeschrittene	8	08.12.2004 10:57
[Erledigt] PHP und MSSQL Server		PHP Tipps 2004	5	04.06.2004 18:03

18.10.2011, 09:46
eagle275 Erfahrener Benutzer Registriert seit: 01.09.2010 Beiträge: 4.561 PHP-Kenntnisse: Fortgeschritten	[Erledigt] ORDER BY im SUB SELECT bei MSSQL Server bringt Fehlermeldung ich bin gerade dabei meine mysql Datenbank (Firmeninterne Anwendung) auf MSSQL-Server zu migrieren. Hauptgrund ist die einfache Verschlüsselung auf Datenbank-Ebene (per Unternehmensrichtlinie vorgeschrieben). Die normalen Abfragen, Spaltentypen und Unicode-Probleme konnte ich lösen.... Nun hänge ich an einer etwas komplizierter gestrickten Abfrage. In meiner Anwendung gibts ein kleines Ticketsystem, über das einerseits das System selbst nach Konsistensprüfungen bestimmte Aufgaben verteilt (einerseits an die Admins, um Einstellungen von Systemen zu ändern, andererseits an die User, falls offensichtliche Fehler gemacht wurden). Dieses Ticketsystem besteht im Grunde aus 3 Tabellen - eine Hilfstabelle, das die festgelegte Auswahl an Gründen enthält, wegen denen Tickets erstellt wurden (nötig, weil die verschiedenen Tickets unterschiedlich behandelt werden müssen) - dann die Tabelle über die eigentlichen Tickets und eine Tabelle, die den Verlauf der Ticketbearbeitung enthält (es ist bei mindestens einem Ticketgrund möglich, über das Ticketsystem Nachrichten zwischen Usern verschiedener Niederlassungen auszutauschen - diese Nachrichten müssen ALLE in ihrer zeitlichen Reihenfolge vorgehalten werden. Das Problem ist die Abfrage, die alle fraglichen offenen Tickets zu einer Userklasse (festgelegt durch Niederlassung + Rechtestufe) auflisten soll. Um die "richtigen" Tickets zu erwischen, ist es erforderlich auch die VerlaufsTabelle mit zu beachten, weil Tickets, die eine Niederlassung an eine andere sendet, sind zwar offen, sind aber momentan der anderen Niederlassung zugeordnet (obwohl die erstere Niederlassung logischerweise auch drin steht). Ich muss also jeweils das allerneueste Verlaufsticket zu einem Ticket beachten. Dazu habe ich mir in mySQL eine etwas längliche Abfrage erstellt, die aber trotz der Verwendung einer SubQuery recht performant läuft. Nun zum Problem .. der MSSQL-Server begrüßt meine Abfrage mit einer Fehlermeldung, zu der ich so recht keine Abhilfe weiß : "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." die zugehörige Abfrage : Code: SELECT * FROM ( SELECT verlauf.* , tickets.customer_id as customer_id,verlauf.ticket_id AS ticketnummer, tickets.abgeschlossen as abgeschlossen, tickets.anhang as anhang, tickets.ticket_id AS thema FROM ( SELECT * FROM ticketverlauf ORDER BY id DESC ) AS verlauf LEFT JOIN tickets ON tickets.id = verlauf.ticket_id WHERE tickets.erledigt = 'nein' AND 1 =1 GROUP BY verlauf.ticket_id ORDER BY verlauf.ticket_id ASC ) AS ticketanzeige WHERE ( flag=1 OR flag=28 or flag=2 ) AND 1=1 bitte nicht wundern über das 1=1 am Ende, die Abfrage wird teils automatisch zusammengesetzt, da ist dieser Zusatz immer recht hilfreich (gewesen) - die verschiedenen flags dienen der Filterung der tickets für meine Berechtigungsstufe (als Beispiel) - auf Wunsch kann ich auch noch die Create-Table Anweisungen liefern Wie löse ich nun das Problem, dass er mein ORDER BY im Inneren nicht mag, das aber die wichtige Sortierung der Verlaufstickets erledigt- zumindest in mysql funktioniert es NICHT, wenn ich das ORDER BY erst außen herum mit erledige (dann nimmt er automatisch immer das erste Verlaufsticket zu jedem Ticket und listet mir Tickets als offen auf, die eigentlich momentan an eine andere Niederlassung versendet wurden und dort erstmal bearbeitet werden müssen ich hab dazu schon im Web bei Microsoft die Doku gelesen und auch im MS-Forum gesucht, aber keine rechte Abhilfe gefunden und momentan fehlt mir ehrlichgesagt die Fantasie, mir die Abfrage anders herum zu bauen - ohne auf Schleifen zu verfallen, die wohl der Performance abträglich wären (erst alle offenen Tickets auswählen, dann jeweils die zugehörigen Verlauftickets einlesen und prüfen, ob das Ticket überhaupt für den jeweiligen User "offen" ist .... ich hätte es schon gern wie bisher per JOINs in einer Abfrage erledigt - andererseits kann ich ja nicht in einem Statement sagen "wähle alle Verlaufstickets, gruppiert nach dem Ticket, sortiert nach Reihenfolge - und dann joine den Rest dazu " __________________ "Irren ist männlich", sprach der Igel und stieg von der Drahtbürste Geändert von eagle275 (18.10.2011 um 09:53 Uhr).


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

18.10.2011, 10:39
eagle275 Erfahrener Benutzer Registriert seit: 01.09.2010 Beiträge: 4.561 PHP-Kenntnisse: Fortgeschritten	hm - ich dachte, genau die Antwort auf deine ersten Fragen stehe schon im Text... speziell 3) -> es wird zuerst ein Ticket-Eintrag geschrieben. Der korrespondiert mit EINEM ticketverlauf Eintrag . Wenn dann ein User das Ticket bearbeitet, wird ein 2ter ticketverlauf-Eintrag erstellt (und bei mindestens einem Ticket"Grund" können es auch viel mehr Nachrichten sein, die im Ticketverlauf quasi hin und her gesendet werden. An jedem Ticketverlauf-Eintrag steht im Feld flag, für welche User-Klasse es bestimmt ist - ich möchte aber nur die Tickets haben, die für die Abfragende User(Klasse) jetzt anstehen. Ohne das order by dort, nimmt die Datenbank den zuerst gefunden ticketverlauf-Eintrag, der zum Ticket passt. Ich seh aber erst am allerletzten Eintrag, ob das Ticket für den abfragenden User(-Klasse) bestimmt ist, oder nicht ... das group by ist aus dem gleichen Grund da, ich brauch jedes Ticket nur einmal .... ok, danke jens - mit deinen Hinweisen scheint es mir gelungen zu sein, die Abfrage entsprechend zu vereinfachen aber ich probier mal kurz was aus und melde mich dann nochmal __________________ "Irren ist männlich", sprach der Igel und stieg von der Drahtbürste Geändert von eagle275 (18.10.2011 um 12:19 Uhr).

18.10.2011, 10:52
Jens Clasen Erfahrener Benutzer Registriert seit: 19.06.2009 Beiträge: 837 PHP-Kenntnisse: Fortgeschritten	Okay - das heißt also, in MySQL missbrauchst Du das kapputte Group by. MySQL ignoriert die Notwendigkeit der funktionalen Abhängigkeit und gibt einfach den erstbesten Wert aus. Um den erstbesten Wert auch zu kriegen, verwendest Du ein Group by. Das das funktioniert ist mehr Zufall, als alles andere. Was Du versuchen solltest ist, nur den letzten Verlaufsstatus des Tickets zu betrachten. Das kannst Du auf zwei Arten erreichen: Code: SELECT v1.whatever FROM ticketverlauf v1 LEFT JOIN ticketverlauf v2 ON v1.ticket_id=v2.ticket_id AND v2.zeitpunkt>v1.zeitpunkt WHERE v2.id IS NULL oder so Code: SELECT v1.whatever FROM ticketverlauf v1 INNER JOIN (SELECT vi.ticket_id, MAX(vi.zeitpunkt) AS dt FROM ticketverlauf vi GROUP BY vi.ticket_id) v2 ON v1.ticket_id=v2.ticket_id AND v1.zeitpunkt=v2.dt Auf beide Ergebnisse kannst Du problemlos alle anderen relevanten Daten drauf joinen. Besser wäre sogar, wenn Du von der Ticket-Tabelle ausgehend joinst. Gruß Jens

18.10.2011, 12:21
eagle275 Erfahrener Benutzer Registriert seit: 01.09.2010 Beiträge: 4.561 PHP-Kenntnisse: Fortgeschritten	ja, habe das jetzt ausgehend von der ticket tabelle gelöst .... und der einfache Join läuft natürlich noch schneller, als der subselect vorher __________________ "Irren ist männlich", sprach der Igel und stieg von der Drahtbürste

18.10.2011, 12:23
Jens Clasen Erfahrener Benutzer Registriert seit: 19.06.2009 Beiträge: 837 PHP-Kenntnisse: Fortgeschritten	Kannst Du das fertige Select mal bitte posten? Gruß Jens

18.10.2011, 12:26
eagle275 Erfahrener Benutzer Registriert seit: 01.09.2010 Beiträge: 4.561 PHP-Kenntnisse: Fortgeschritten	Code: SELECT *,tickets.id as ticketnummer, tickets.ticket_id as thema FROM tickets LEFT JOIN ticketverlauf ON tickets.id=ticketverlauf.ticket_id WHERE erledigt='nein' AND ( flag=1 OR flag=28 or flag=2 ) AND 1=1 ORDER By tickets.id ASC, ticketverlauf.id DESC ich werd die Abfrage mal im Auge behalten, ob ich wirklich diejenigen Tickets erhalte, die ich haben will __________________ "Irren ist männlich", sprach der Igel und stieg von der Drahtbürste

18.10.2011, 12:28
Jens Clasen Erfahrener Benutzer Registriert seit: 19.06.2009 Beiträge: 837 PHP-Kenntnisse: Fortgeschritten	Irgendwie passt das nicht zu Deiner Beschreibung oben. Ich denke, im Verlauf hast Du zu jedem Ticket mehrere Stati stehen? Gruß Jens

18.10.2011, 12:38
eagle275 Erfahrener Benutzer Registriert seit: 01.09.2010 Beiträge: 4.561 PHP-Kenntnisse: Fortgeschritten	nicht zwingend .... anfangs wird zu jedem ticket genau 1 verlaufs-Eintrag erzeugt. wenn der nun bearbeitet wird, dann kommt ein Verlaufseintrag hinzu __________________ "Irren ist männlich", sprach der Igel und stieg von der Drahtbürste

18.10.2011, 12:53
Jens Clasen Erfahrener Benutzer Registriert seit: 19.06.2009 Beiträge: 837 PHP-Kenntnisse: Fortgeschritten	Und genau das ist das Problem bei Deiner Query. Nimm an, Deine Niederlassung ist für den ersten Verlaufsstatus zuständig, nicht aber für den zweiten. Deine Query würde in diesem Fall den ersten zurück geben und den zweiten unterschlagen. Gruß Jens