Row Number Function wie bzw. an welcher Stelle im Query einsetzen

[skyerjoe]

Hallo miteinander

Ich bin absoluter Änfänger was Datenbanken angeht und habe deshalb mal eine Frage.

Wo muss ich die Row Number Function einsetzen bei einem Query:

Hier mal mein Query in verkürzter Form :
SELECT
DISTINCT
(RELFIRMA.FIRMANR),
RELFIRMA.FIRMANR,
RELANSCH.STRASSE,
RELANSCH.ORT,
RELANSCH.LAND,
RELANSCH.STAAT,
RELFIRMA.NAME,
RELZTLB.KTXT Lieferbedingungen,
RELZTSPE.KTXT \"Spedition/Versand\",
RELZTZB.KTXT Zahlungsbedingungen,
reltext.betreff
FROM (INFOR.RELFIRMA RELFIRMA
left outer join infor.reltext reltext on
relfirma.textnr = reltext.textnr
INNER JOIN INFOR.RELADRESSE RELADRESSE
ON (RELFIRMA.FIRMANR = RELADRESSE.FIRMANR)
inner JOIN INFOR.RELACP RELACP
ON (RELFIRMA.FIRMANR = RELACP.MNR)
inner join INFOR.RELANSCH RELANSCH
ON (RELADRESSE.ANSCHRIFTNR = RELANSCH.ANSCHRIFTNR))
left outer JOIN INFOR.RELZTLB RELZTLB
ON (RELACP.TEXT0 = RELZTLB.ZTKEY)
left outer JOIN INFOR.RELZTSPE RELZTSPE
ON (RELACP.TEXT1 = RELZTSPE.ZTKEY)
left outer JOIN INFOR.RELZTZB RELZTZB
ON (RELACP.ZBED = RELZTZB.ZTKEY)
WHERE RELFIRMA.VERWENDUNG1 = '1'
AND RELANSCH.VERWENDUNG1 = '1'
AND RELFIRMA.FIRMANR NOT LIKE 'I%'
AND RELZTSPE.Sprache = 'de'
AND RELZTZB.Sprache = 'de'
AND RELZTLB.Sprache = 'de'"


Mein Anliegen bei zwei Tabellen ( immer für einen Datensatz.. also es darf keinen doppelten Datensatz mit 2 Werten zB. für RELANSCH.VERWENDUNG1 geben )

AND RELANSCH.VERWENDUNG1 = '1'


Ich will das er wenn er den Wert 1 in der Tabelle findet diesen nimmt wenn er ihn aber nicht findet, soll er den nächsthöheren nehmen.

AND RELZTSPE.Sprache = 'de'
AND RELZTZB.Sprache = 'de'
AND RELZTLB.Sprache = 'de'"

Hier soll er den Wert 'de' finden wenn er vorhanden ist ansonsten 'us'.


In einem anderen Forum hat man mir gesagt, das geht mit der 'Function rownum bloss habe ich keine Ahnung wie oder wo oder an welche Stelle ich diese Funktion setzen müßte.

Wäre dankbar wenn mir da jemand helfen könnte.

grüße john

 

[ukulele]

Du musst in jedem Fall innerhalb von ROW_NUMBER() mit PARTION BY arbeiten. Das Ergebnis nimmst du dann als Subselect und zeigst von diesem nur Zeile 1 an.

Vereinfachtes Beispiel:

SELECT t.* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY RELANSCH.VERWENDUNG1 PARTITION BY RELANSCH.VERWENDUNG1) AS zeilennummer, RELANSCH.VERWENDUNG1 FROM tabelle ) t WHERE t.zeilennummer = 1

 

[skyerjoe]

Hallo Ukulele,

Ich habe mich entschieden eine Rank Subfunction zu nehmen nach ein bisschen Recherche und Hilfe vom Oracle Forum

Ich habe hier mal einen Beispiel Query aus unserem System mit der Rank Function oben aber das klappt irgendwie nicht.

Hier mal mein Query mit der Rank function die oben steht aber nicht funktioniert mit dem unteren Query :

WITH    got_rnk    AS
(
    SELECT   company, ADRESSID, lang1
    ,       DENSE_RANK () OVER ( PARTITION BY  company
                                 ORDER BY      ADRESSID
                                 ,             CASE  lang1
                                                   WHEN  'de'  THEN  'A'
                                                   WHEN  'us'  THEN  'B'
                                                               ELSE  'Z'
                                               END
                               )  AS rnk
    FROM   got_rnk
)
)

SELECT   company, ADRESSID   lang1

FROM  got_rnk

WHERE  rnk  = 1
ORDER BY  company

SELECT

  (RELFIRMA.FIRMANR) as company,
RELADRESSE.ADRESSENR as ADRESSID,
  RELZTSPE.Sprache as lang1,
  RELZTZB.Sprache as lang2,
  RELZTLB.Sprache as lang3
  FROM (INFOR.RELFIRMA RELFIRMA
left  outer  join infor.reltext reltext on
relfirma.textnr = reltext.textnr
INNER JOIN INFOR.RELADRESSE RELADRESSE
ON (RELFIRMA.FIRMANR = RELADRESSE.FIRMANR)
inner JOIN INFOR.RELACP RELACP
ON (RELFIRMA.FIRMANR = RELACP.MNR)
inner  join  INFOR.RELANSCH RELANSCH
ON (RELADRESSE.ANSCHRIFTNR = RELANSCH.ANSCHRIFTNR))
right outer JOIN INFOR.RELZTLB RELZTLB
ON (RELACP.TEXT0 = RELZTLB.ZTKEY)
right outer JOIN INFOR.RELZTSPE RELZTSPE
ON (RELACP.TEXT1 = RELZTSPE.ZTKEY)
right outer JOIN INFOR.RELZTZB RELZTZB
ON (RELACP.ZBED = RELZTZB.ZTKEY)
WHERE RELFIRMA.VERWENDUNG1 = '1'
AND RELANSCH.VERWENDUNG1 = '1'
AND RELFIRMA.FIRMANR NOT LIKE 'I%'
--AND RELZTSPE.Sprache = 'de'
--AND RELZTZB.Sprache = 'de'
-- AND RELZTLB.Sprache = 'de'

Und hier mal ein Auszug aus dem Ergebnis des Queries ( ohne Rank Function ) und Anmerkungen was es erreichen soll ( die Rank function sollte das eigentlich abdecken):




Meine wichtigste Frage ist, wo ich die Rank function hinsetzten muss, damit sie in meinem Query funktioniert.

Bitte hab Nachsicht. Ich bin was Datenbanken angeht nicht der Crack und es kann sein das ich vielleicht irgendwelche Infos vergesssen habe die du brauchst.

Schonmal vielen Dank für deine Hilfe

grüße skyerjoe

 

[ukulele]

Also dein Code mit DENSE_RANK() und WITH macht im WITH einen SELECT FROM got_rnk, dabei definiert WITH erst got_rnk. Das ist Rekursion, das kann man mit WITH machen aber ich glaube das ist hier nicht gewollt. Du möchtest an dieser Stelle auf deine Datentabelle zugreifen.

Abgesehen davon brauchst du WITH nicht zwingend. Ohne dein Query mit den ganzen Joins jetzt durch zu gucken suchst du im Prinzip folgendes:

SELECT
DENSE_RANK () OVER (
PARTITION BY RELFIRMA.FIRMANR
ORDER BY RELADRESSE.ADRESSENR,( CASE RELZTSPE.Sprache WHEN 'de' THEN 1 WHEN 'us' THEN 2 ELSE 9 END )
) AS rnk,
---ungeprüft---
RELFIRMA.FIRMANR as company,
RELADRESSE.ADRESSENR as ADRESSID,
RELZTSPE.Sprache as lang1,
RELZTZB.Sprache as lang2,
RELZTLB.Sprache as lang3
FROM (INFOR.RELFIRMA RELFIRMA
left outer join infor.reltext reltext on
relfirma.textnr = reltext.textnr
INNER JOIN INFOR.RELADRESSE RELADRESSE
ON (RELFIRMA.FIRMANR = RELADRESSE.FIRMANR)
inner JOIN INFOR.RELACP RELACP
ON (RELFIRMA.FIRMANR = RELACP.MNR)
inner join INFOR.RELANSCH RELANSCH
ON (RELADRESSE.ANSCHRIFTNR = RELANSCH.ANSCHRIFTNR))
right outer JOIN INFOR.RELZTLB RELZTLB
ON (RELACP.TEXT0 = RELZTLB.ZTKEY)
right outer JOIN INFOR.RELZTSPE RELZTSPE
ON (RELACP.TEXT1 = RELZTSPE.ZTKEY)
right outer JOIN INFOR.RELZTZB RELZTZB
ON (RELACP.ZBED = RELZTZB.ZTKEY)
WHERE RELFIRMA.VERWENDUNG1 = '1'
AND RELANSCH.VERWENDUNG1 = '1'
AND RELFIRMA.FIRMANR NOT LIKE 'I%'
--AND RELZTSPE.Sprache = 'de'
--AND RELZTZB.Sprache = 'de'
-- AND RELZTLB.Sprache = 'de'

Da kommen dann immernoch alle Datensätze zurück aber mit einer Nummer die die zeigt, der wievielte Datensatz mit den selben Kriterien das ist. Das solltest du erstmal testen und prüfen.

Diese Daten filterst du jetzt noch auf den jeweils ersten Datensatz. Dazu kannst du auch WITH benutzen (A), du kannst aber auch alles in FROM reinpacken (B).

Beispiel A:

WITH tabelle AS ( --<dein Select>-- )
SELECT tabelle.* FROM tabelle WHERE tabelle.rnk = 1

Beispiel B:

SELECT tabelle.* FROM ( --<dein Select>-- ) tabelle WHERE tabelle.rnk = 1

 

[skyerjoe]

Hallo Ukulele

Achso die dense Rank function wird zu einem select objekt gemacht oder ?

Ich mußte deine Function leicht abändern ( N"||) das diese error Meldung gekommen ora-12704: character set mismatch ( hoffe das ist richtig) und den CASE hab ich auf das Feld RELZTZB.Sprache ( lang2) umgestellt, da hier immer Values vorhanden sind und nie "Null":

SELECT
DENSE_RANK () OVER (
PARTITION BY RELFIRMA.FIRMANR
ORDER BY RELADRESSE.ADRESSENR,( CASE RELZTZB.Sprache  WHEN N'' || 'DE' THEN 1  WHEN N'' || 'US' THEN 2 ELSE 9 END )

Hier ein Auszug aus dem Ergebnis:






Das sieht schonmal nicht schlecht aus aber glaube da Fehlt nochwas. Man sieht das einem 'us' und 'de' mit derselben Adresse denselben Rang zuteilt .... Evtl. hab ich das auch nicht ganz so gut erklärt.
Oder es liegt vielleicht an meinen joins.

Ich werde mal eine Testtable in meiner DEV-Instance hochziehen.... oder du weisst woran es noch happert dann könnte ich mir das Aufziehen sparen.

Btw. evtl. da ein paar tipps wie ich schnell ein paar tables inserte oder create ? ich habe den sql developer evtl. kann ich ja was aus meiner prod exportieren für meine Dev.
ich weiss aber nur wie ich einen select exportiere, einzelne tables schemas weiss ich nicht ....

beste grüße skyerjoe

 

[ukulele]

Ich glaube es liegt eher an RANK(). Wenn ich das richtig sehe gibt RANK() immer die Platzierung zurück, also bei gleichen Werten können sich mehrere Zeilen einen Platz teilen. DENSE_RANK() sorgt nur dafür das keine Lücken entstehen, also wenn sich 2 Datensätze Platz 1 teilen geht es mit 2 und nicht mit 3 weiter. Bei ROW_NUMBER() wird durchgezählt, auch wenn alle Werte gleich sind. Versuch es mal mit ROW_NUMBER(), das müsste auch Oracle kennen.

 

[skyerjoe]

Also im Test Szenario geht die got_rnk Function aber genauso wie sie soll. Aber deine Function ist doch genau dieselbe oder ?

Ich werde mich mal an der Row Number variante versuchen .. mal schaun ob ichs hinkriege.


grüsse

 

[ukulele]

Du musst nicht viel verändern nur DENSE_RANK mit ROW_NUMBER tauschen.
ROW_NUMBER

 

[skyerjoe]

Hallo Ukulele

In Filmen würde man sagen : Kann es so einfach sein ?

Aber stimmt es klappt mit Row_Number ... aber es passt noch nicht ganz hier mal ein Beispiel aus dem Ergebnis




Wie man sieht stuft er die 'us' Datensätze höher ein als die 'de', aber genau dass soll er nicht

Weisst du woran das Liegen kann ?

grüße skyerjoe

 

[ukulele]

Vermutlich ist dein CASE im ORDER BY case sensitive. Du kannst das CASE ja mal im Select mit anzeigen lassen und siehst dann, ob die richtigen Ziffern zugeordnet werden.

 

[skyerjoe]

Hallo Ukulele

Sorry für die Anfängerfrage ...

wie kann ich den CASe anzeigen lassen in meinem Select Statement ?

grüße skyerjoe

 

[ukulele]

SELECT
splate1,
( CASE RELZTZB.Sprache WHEN N'' || 'DE' THEN 1 WHEN N'' || 'US' THEN 2 ELSE 9 END ) AS was_auch_immer,
spalte2,
spalte3,
[...]
FROM tabelle

 

[skyerjoe]

Hallo Ukulele,

sorry das ich so spät das Thema wieder aufgreife aber wir haben eigentlich nie genug Zeit ( und wir sind eigentlich echt nur Aushilfs Datenbank Menschen ) und manches wird dann vom Chef erzwungen und das muss dann gemacht werden

Glaube es haut soweit jetzt erstmal hin ... hattest Recht mit dem "Case Sensitive"

Ich muss das aber erst nochmal prüfen mit unsererer Abfrage

Schonmal fettes danke an der Stelle

grüße skyerjoe

 

[skyerjoe]

Hallo Ukulele,

Wo muss ich die where Clause für das erstellt Feld rnk in meinem query setzen.

Habe es versucht unten in Abschnitt --- ungeprüft -- aber das klappt nicht.

Gibt es generell eine Regel an welcher Stelle ich Konditionen setzen muss für Funktionen wie Rownumber

grüße skyerjoe

 

[akretschmer]

Row_number() ist eine der vielen Window-Funktionen. Falls die Oraggle-Doku diese nicht gut genug in der Syntax beschreibt schaut man halt mal bei der Konkurrenz nach ;-)

PostgreSQL: Documentation: 9.5: Value Expressions