Zeilen mit größter ID je Eintrag ausgeben

[squib]

Hallo,

ich habe eine Datenbank, in der Datensätze ähnlich wie in einer Orderstruktur abgelegt sind. Als "Ordner" fungieren ID-Nummern. Jede ID-Nummer kann mehrere Vorgänger haben (analog einem Unterverzeichnis) und jeder Vorgänger kann wiederum mehrere Datensätze haben, die auch gleich heißen können. Die nachfolgend umschriebene Abfrage (stark vereinfacht und mit geänderten Namen) ergibt immer mehrere Datensätze für die Spalte LFD_NR. Ich möchte aber für jede LFD_NR immer nur den Datensatz mit der größten ID-Nummer.

Die Vorgänger jeder ID-Nummer sind in einer eigenen Tabelle abgelegt (jede ID ist auch ihr eigener Vorgänger):

Beispiel Tabelle PFAD:

ID          Vorgänger
21793      21030
21793      21031
21793      21032
21793      21033
21793      21793

Die eigentlichen Datensätze (LFD_NR) stehen in einer anderen Tabelle:

Beispiel Tabelle DATEN:

LFD_NR      ID
10          21030
10a         21030
20          21030
30          21030
40          21030
50          21030
10          21031
10a         21031
20          21031
40          21031
10          21032
50          21032
20          21793
40          21793
50          21793

Die ID, mit der begonnen werden soll, hole ich aus einer dritten Tabelle:

Beispiel Tabelle STAMM:

Nummer      Version      ID
123456      5            21793

Die Verknüpfung erfolgt über die ID, der Anwender gibt mittels Parameterabfrage "Nummer" und "Version" vor. Verknüpft wird mit INNER JOIN, wobei STAMM.ID = PFAD.ID und PFAD.Vorgänger = DATEN.ID

Wenn ich so abfrage, bekomme ich im obigen Beispiel (sortiert):

LFD_NR      ID
10          21030
10          21031
10          21032
10a        21030
10a        21031
20          21030
20          21031
20          21793
30          21030
40          21030
40          21301
40          21793
50          21030
50          21032
50          21793

Ich möchte aber:

LFD_NR      ID
10          21032
10a        21031
20          21793
30          21030
40          21793
50          21793

Es soll also jede LFD_NR nur einmal vorkommen und zwar nur der Datensatz mit der jeweils höchsten ID-Nummer.

Ich google seit zwei Tagen rauf und runter wegen einer passenden Lösung, aber entweder habe ich nichts gefunden oder ich verstehe nicht genug von SQL, um es zu kapieren (trifft wohl eher zu). Kann mir jemand helfen?

Vielen Dank im Voraus!

 

[PLSQL_SQL]

Hy,

sollte gehen, wenn du deine bisherige Abfrage als INLINE-VIEW verwendest.

select tb.LFD_NR
         ,max(ID) as ID
 
from
(
   --deine bisherige Abfrage
)tb
 
GROUP BY tb.LFD_NR

Lg

 

[ukulele]

Ich bin erlich gesagt irgendwo in der Erklärung stecken geblieben, die bisherige Abfrage wäre sehr hilfreich. Das von PL könnte aber funktionieren.

 

[squib]

Ok, sorry, die deutlich umfangreichere Abfrage habe ich weggelassen, um keine unnötige Verwirrung zu stiften. Hat wohl nicht funktioniert...
Ich habe die Original-Abfrage jetzt mal stark vereinfacht, so daß sie im Wesentlichen immer noch dasselbe Ergebnis ausgibt:

Select DATEN.LFD_NR, DATEN.SORT_NR, STAMM.ID, DATEN.ID From STAMM
Inner Join PATH On STAMM.ID = PATH.ID
Inner Join DATEN On PATH.VORGAENGER_ID = DATEN.ID
Where STAMM.ID = 21793
Order By DATEN.SORT_NR

Die Spalte SORT_NR ist für das Problem eigentlich unwichtig, sie dient nur einer eindeutigeren Sortierung.
Wenn ich diese Abfrage wie angegeben in ein Inline View einbette, bekomme ich gar keine Ausgabe mehr. Erst wenn ich die MAX-Funktion entferne und alles ab dem WHERE außerhalb der Klammer plaziere, funktioniert es, aber das Ergebnis ist dasselbe wie vorher. Leider habe ich hier keinen vernünftigen SQL-Editor, sondern nur ein SQL-Plugin in der DB-Anwendung, das mir keine brauchbaren Fehlermeldungen ausgibt (um nicht zu sagen gar keine). Dafür zeigt es die Abfrage als grafische Verknüpfung an.

Wenn ich das mit dem Inline View richtig verstanden habe, sucht die MAX-Funktion hier auch nicht die größte ID pro LFD_NR (eigentlich die größte VORGAENGER_ID), sondern nur die größte im ganzen Ergebnis.

P.S.: Hab es jetzt nochmal mit MS Query probiert. Sobald ich die MAX-Funktion wie beschrieben einfüge, bekomme ich die Fehlermeldung "Could not add the table '(Select'."
Dasselbe wenn ich ganz am Schluß das GROUP BY einfüge.

 

[ukulele]

Das hier sollte aber funktionieren:

SELECT    tabelle.LFD_NR,
        max(tabelle.ID) AS ID
FROM    (    SELECT    DATEN.LFD_NR,
                    DATEN.SORT_NR,
                    STAMM.ID,
                    DATEN.ID
            FROM    STAMM
            INNER JOIN [PATH] ON STAMM.ID = [PATH].ID
            INNER JOIN DATEN ON [PATH].VORGAENGER_ID = DATEN.ID
            WHERE    STAMM.ID = 21793 ) tabelle
GROUP BY tabelle.LDF_NR
ORDER BY tabelle.LFD_NR

ORDER BY ist in der INLINE Funktion nicht möglich. Auch sollte max() die maximale ID je LFD_NR liefern.

 

[squib]

Jepp, das funktioniert tatsächlich! Vielen Dank nochmal, das bringt mich einen großen Schritt weiter.
Was ich noch nicht ganz verstehe: Wenn ich die Abfrage auf mehr Spalten erweitere, muß ich für alle die MAX()-Funktion angeben, außer für LFD_NR, sonst bekomme ich Fehlermeldungen. Das Select in der Klammer wird sonst nicht erkannt. Ist das normal?

 

[ukulele]

Alle angezeigten Spalten müssen entweder in der GROUP BY Klausel aufgeführt sein oder in eine Aggregatfunktion sein ( min, max, avg, sum ). Mit GROUP BY kategoriesierst du ja und wenn nicht jeder Wert der Spalte als Kategorie gelten soll (also in der GROUP BY Klausel steht) muss SQL irgendwie wissen, welchen der Werte aus der Spalte es denn nehmen soll.

Soweit ich weiss gibt es bei MySQL die Möglichkeit, Spalten einfach anzugeben. In allen anderen SQL Varianten wird er auf eine saubere Angabe der Spalte in GROUP BY bestehen.

 

[squib]

Stimmt eigentlich, so betrachtet klingt das logisch. Hab mich durch die Fehlermeldung wohl verwirren lassen.

 

[squib]

Jetzt hab ich doch noch ein Problem mit der Abfrage: Bei der obigen Lösung muß ich wie gesagt für alle zusätzlichen Spalten Aggregatfunktionen verwenden. Dadurch werden aber Ergebnisse aus verschiedenen Zeilen vermischt. Ich möchte aber, daß die Zeilen mit den höchsten IDs unverändert ausgegeben werden.

Wenn ich das Ergebnis der Abfrage aus Beitrag #4 zugrunde lege, dann soll eigentlich für jeden Wert von LFD_NR die Zeile mit der größten ID gesucht werden. Diese Zeile soll dann unverändert ausgegeben werden.

Nochmal zur Verdeutlichung, mit einer zusätzlichen Wertespalte:

Die Abfrage aus #4 könnte z.B. folgendes ausgeben:

LFD_NR    ID      Beliebiger_Wert
10        21030   15,7
10        21031   16,9
10        21032   9,5
10        21793   3,8
20        21030   10,9
20        21032   7,5

Mit der Lösung aus #5 muß ich für die dritte Spalte z.B. MAX(Beliebiger_Wert) schreiben und bekomme dann als Ergebnis:

LFD_NR    ID      Beliebiger_Wert
10        21793   16,9
20        21032   10,9

Hier werden also Werte aus verschiedenen Zeilen vermischt. Richtig wäre ja:

LFD_NR    ID      Beliebiger_Wert
10        21793   3,8
20        21032   7,5

Ich habe schon mit allem möglichen experimentiert, z.B. mit OVER-Klausel und PARTITION BY, aber wieder mal komme ich einfach auf keine Lösung, die die Zeilen unverändert läßt. Könnte mir da nochmal jemand auf die Sprünge helfen?

 

[PLSQL_SQL]

Hy,

probie das hier mal...

Select mastertabelle.*,
          s.beliebiger_Wert
 
FROM
(
 
SELECT    tabelle.LFD_NR,
          max(tabelle.ID) AS ID
 
FROM    (    SELECT    DATEN.LFD_NR,
                    DATEN.SORT_NR,
                    STAMM.ID,
                    DATEN.ID
            FROM    STAMM
            INNER JOIN [PATH] ON STAMM.ID = [PATH].ID
            INNER JOIN DATEN ON [PATH].VORGAENGER_ID = DATEN.ID
            WHERE    STAMM.ID = 21793
          ) tabelle
GROUP BY tabelle.LDF_NR
 
) mastertabelle
  JOIN STAMM s ON s.ID = mastertabelle.ID

Lg

 

[ukulele]

Das kann glaube ich nur funktionieren wenn s.ID eindeutig ist. Ansonsten werden wieder einige Zeilen doppelt ausgegeben. Ohne Testdaten ist das jetzt für mich aber auch etwas schwer.

Vieleicht kannst du mal deine Tabelle als CREATE Anweisung und deine Daten als INSERT Anweisung posten, das ist dann super einfach zum testen

 

[squib]

Ob s.ID eindeutig ist, kann ich erst sagen, wenn ich verstanden habe, wie s.ID überhaupt entsteht. Das hier geht so langsam über meinen SQL-Horizont hinaus . Zumindest kann für jeden Wert von LFD_NR jede ID nur einmal vorkommen.

Na ich werde das jetzt einfach mal versuchen umzusetzen, und dann Bericht erstatten obs geklappt hat. Man muß ja nicht immer alles verstehen, Hauptsache es funktioniert

Mit Testdaten als CRATE/INSERT-Statements tue ich mir schwer. Kann man sowas irgendwie automatisch generieren lassen?


Ergänzung:
Hab das jetzt mal probiert, aber mit der Bedeutung von 's' komme ich momentan nicht klar. Warum steht oben 's.Beliebiger_Wert' und unten 'JOIN STAMM s'? Und fehlt oben nicht ein FROM?
's' ist doch ein Alias wenn ich das richtig verstehe. Aber die Spalte "Beliebiger_Wert" stammt aus der Tabelle DATEN, nicht aus STAMM. Muß ich das dann anpassen (wie)? Vielleicht muß ich wirklich mal Beispieldaten kreieren. Ich weiß aber momentan nicht, wie ich das machen soll. Außerdem wird es dann noch komplexer.

 

[PLSQL_SQL]

Hy,

ja oben fehlte "FROM" dies habe ich soeben ergänzt. Weiters wusste ich nicht, dass "Beliebiger_Wert" in der Datentabelle steht und nicht in der Tabelle "STAMM".

Dazu müsstest du nun zu dem ursprünglichen Stmt (hier "mastertabelle" genannt) die Datentabelle anstatt der "STAMM" - Tabelle joinen. Und ja "s" ist nur ein Alias!!!!

Lg

 

[ukulele]

Ich verliere auch etwas den Überblick, ich bin mir aber sicher das das gewünschte geht! Du müsstest entweder mal Testdaten + Tabellen posten oder die Tabellen / Spalten Struktur erläutern und sagen, was genau raus kommen soll

 

[squib]

Sorry für die langen Pausen, bin momentan ziemlich eingespannt mit anderen Sachen.

Erläutern hab ich ja versucht, aber vielleicht habe ich es zu sehr vereinfacht. Was ich evtl. recht einfach hinkriegen könnte,wäre ein Export von Beispieldaten nach Excel und eine grafische Darstellung der Tabellenverknüpfungen. Würde das reichen?

EDIT:
Ich habe jetzt mal Bespieldaten exportiert und in eine kleine Access 2003 Datenbank geschrieben. Dazu habe ich auch eine Abfrage erstellt, die mein Problem nachbildet. Ich muß noch etwas dran rumfeilen, aber dann werde ich versuchen, das hier hochzuladen. Die DB ist ca. 1,5 MB groß. Gibt es hier ein Limit für Uploads?