Max-Werte und Sequence-Number in Query

[tm2311]

Hallo Zusammen,

ich benötige Hilfe beim Zusammenbau einer Query. Ich möchte in meinem Programm GUT und SCHLECHT
Läufe sehen die von einem Prüfprogramm in die Datenbank geschrieben werden.

SELECT COUNT(DISTINCT [SERIENNUMMER]) AS ANZ_IO
FROM TABLE1
WHERE VORGANGSNUMMER = '1234
AND BOOK_STATE = '0'

SELECT COUNT(DISTINCT [SERIENNUMMER]) AS ANZ_IO
FROM TABLE1
WHERE VORGANGSNUMMER = '1234
AND BOOK_STATE = '0'

Diese Querys oben liefern mir genau dieses Ergebnis, allerdings wird hier bei dem Sonderfall, dass das wenn eine Seriennummer doppelt geprüft wird, auch ein weiterer BOOK_STATE in die Datenbank geschrieben wird und dieser bei der Abfrage als Anzahl IO bzw. NIO mit angezeigt wird.
Ich möchte aber das nur die letzte Speicherung eines Prüflaufs angezeigt wird.

Hierfür gibt es in der Tabelle eine SEQUENCE-NUMMER die ich verwenden möchte um nur den aktuellsten Eintrag zu sehen. Hier habe ich jedoch Probleme beim Zusammenbau der Query.

SELECT DISTINCT [SERIAL_NUMBER], MAX(SEQUENCE_NUMBER)
FROM Table1
WHERE VORGANGSNUMMER = '1234
AND BOOK_STATE = '0'
group by [SERIAL_NUMBER]

Diese Query liefert mir die aktuelle SEQUENCE Nummer zu allen IO Buchungen für eine SERIAL_NUMBER.
Hier müsste nun irgendwie erst genannte Query mit Count mit verschachtelt werden damit ich die genaue aktuelle Anzahl der IO Buchungen bekomme.

Vielen Dank

 

[akretschmer]

Deine 2 zuerst genannten Queries sind a) gleich und b) syntaktisch falsch (das 1234 hat vorne ein Hochkomme und hinten nicht).

Wenn Du einfach mal Beispieldaten zeigen würdest und dazu Dein Wunschresultat wäre erheblich verständlicher, was Du willst.

 

[tm2311]

Hallo @akretschmer : Mit der Syntax haben Sie recht. Das fehlende Hochkomma war ein Fehler beim kopieren.

Oben sind nun Beispielwerte.
Ich möchte die GUT und SCHLECHT Buchungen (BOOK_STATE 0 oder 1) zur Station_Number 0123456 und WORKORDER_NUMBER 2021 haben.
Bei oben genannten Querys bekomme ich zwar die Gut und Schlecht Buchungen angezeigt, jedoch alle und das ist falsch.
Anhand der SEQUENZCE_NUMBER ist zu erkennen, welche Buchung als letztes durchgeführt wurde.

Mit den gezeigten Beispieldatum müsste das Ergebnis für IO Buchung 2 und für NIO Buchung 0 lauten.
Bei meiner Query lautet das Ergebnis für IO 5 und für NIO 2, weil die höchste SEQ-NUMBER nicht berücksichtigt wird.

 

[akretschmer]

Solche Bilder haben den massiven Nachteil, daß man diese nicht einfach via Copy&Paste in eine eigene Tabelle kopieren kann, daher stark vereinfacht auf das wesentliche:

edb=*# select * from tm2311 ;
  sn  | state | seq
------+-------+-----
 1234 |     0 |   3
 1234 |     1 |   2
 1234 |     0 |   1
 1234 |     0 |   0
 5678 |     0 |   2
 5678 |     0 |   1
 5678 |     1 |   0
(7 rows)

edb=*# with tmp as (select sn, state, seq, row_number() over (partition by state order by seq desc) from tm2311) select sn, state, seq from tmp where row_number = 1 ;
  sn  | state | seq
------+-------+-----
 1234 |     0 |   3
 1234 |     1 |   2
(2 rows)

edb=*#

 

[tm2311]

Viele Dank.
Ich verstehe leider den ersten Abschnitt nicht
edb=*# with tmp as

 

[Walter]

edb=*#

Das ist der Eingabeprompt der Datenbank. Danach fängt das SQL-Statement an.

 

[akretschmer]

das "edb=*#" ist der Prompt meiner Datenbank, das "with tmp as" der Begin des Statements (auch bekannt als CTE - Common Table Expression).

 

[tm2311]

Wenn ich folgende Query verwenden möchte:

with tmp as (select SERIAL_NUMBER, BOOK_STATE, SEQUENCE_NUMBER, row_number() over (partition by BOOK_STATE order by SEQUENCE_NUMBER desc)
from MYTABLE) select SERIAL_NUMBER, BOOK_STATE, SEQUENCE_NUMBER from tmp where row_number = 1 ;

bekomme ich die Fehlermeldung: Kein Spaltenname wurde für die Spalte 4 von "tmp" angegeben.

 

[akretschmer]

Spalte 4 ist das Ergebniss von row_number(), möglicherweise erwartet M$SQL hier einen Alias. PostgreSQL erwartet keinen.

 

[akretschmer]

edb=*# with tmp as (select sn, state, seq, row_number() over (partition by state order by seq desc) as das_ist_mein_super_alias from tm2311) select sn, state, seq from tmp where das_ist_mein_super_alias = 1 ;
  sn  | state | seq
------+-------+-----
 1234 |     0 |   3
 1234 |     1 |   2
(2 rows)

Besser?

 

[tm2311]

Query umgebaut es funktioniert. Was mir jedoch noch nicht klar ist, wie oben beschrieben:
Bei Ihrer Query erhalte ich als Ergebnis den letzten State zurück, aber ich will ja die Gesamtanzahl aller letzten States haben.
Bedeutet bei den 7 Einträgen befinden sich 2 unterschiedliche SERIENNUMMERN die jeweils einen aktuellen Status anhand der Sequenz haben. Ergebnis müsste also 2 IO Buchungen sein.

 

[akretschmer]

so?

edb=*# with tmp as (select sn, state, seq, row_number() over (partition by sn,state order by seq desc) as das_ist_mein_super_alias from tm2311) select sn, state, seq from tmp where das_ist_mein_super_alias = 1 ;
  sn  | state | seq
------+-------+-----
 1234 |     0 |   3
 1234 |     1 |   2
 5678 |     0 |   2
 5678 |     1 |   0
(4 rows)

edb=*#

 

[tm2311]

Fast perfekt.
Mich interessiert aber die Summe der IOs zu allen Seriennummern. Hier müsste ein COUNT eingebaut werden. Das das Ergebnis mir dann eine 2 zurückliefert für 2 IO Buchungen.
Da sagt er mir aber: Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

Meiner Query:

with tmp as (select COUNT(SERIAL_NUMBER), BOOK_STATE, SEQUENCE_NUMBER, row_number() over (partition by SERIAL_NUMBER, BOOK_STATE order by SEQUENCE_NUMBER desc) as AMOUNT
from MYTABLE WHERE BOOK_STATE = '0')
select SERIAL_NUMBER, BOOK_STATE, SEQUENCE_NUMBER from tmp where AMOUNT = 1

 

[akretschmer]

Fehler ist klar: du aggregierst, gruppierst aber nicht nach den nicht-aggregierten Spalten. Das kann nur MySQL - indem es den Fehler ignoriert und ein zufälliges Result liefert.

also so?

edb=*# with tmp as (select sn, state, seq, row_number() over (partition by sn,state order by seq desc) as das_ist_mein_super_alias from tm2311) select sn, count(state) from tmp where das_ist_mein_super_alias = 1 group by sn ;
  sn  | count
------+-------
 1234 |     2
 5678 |     2
(2 rows)

 

[tm2311]

with tmp as (select SERIAL_NUMBER, BOOK_STATE, SEQUENCE_NUMBER, row_number() over (partition by SERIAL_NUMBER, BOOK_STATE order by SEQUENCE_NUMBER desc) as AMOUNT
from MYTABLE WHERE BOOK_STATE = '0')
select COUNT([BOOK_STATE]) as IO from tmp where AMOUNT = 1

So passt es. Ich sage 1000x danke.