Oracle 10g, Select, Order By, gib mir die drittletzte Zeile

[MudGuard]

Hi,

gibt es eine Möglichkeit, mit einem select (natürlich mit order by, so daß eine Ordnung gegeben ist) die drittletzte Zeile der Ergebnis-Menge auszuwählen?

SELECT id,starttime FROM tabelle WHERE name='bla' ORDER BY starttime

gibt mir das wunderschön sortierte Ergebnis aus.
Allerdings alle Zeilen.

Bei MySQL ginge das ja mit LIMIT, aber bei Oracle?

Mein naiver Ansatz mit ROWNUM = 3 im WHERE-Teil bringt ein leeres Ergebnis (Klar, durch diese Einschränkung hat die Ergebnismenge nur noch eine Zeile, und die hat ROWNUM = 1, ist also nicht die mit ROWNUM = 3, woraufhin die endgültige Ergebnismenge leer ist ...).

Mit einem dreifach geschachtelten Select scheine ich das zu bekommen, was ich will:

SELECT id, starttime FROM
  (
    SELECT id, starttime FROM
      (
        SELECT id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
      )
    WHERE ROWNUM < 4 ORDER BY starttime ASC
  )
WHERE ROWNUM < 2

Aber ich finde das einerseits ziemlich umständlich mit den drei ineinandergeschachtelten selects, andererseits bin ich mir nicht sicher, ob das auch wirklich (bei Anwendung auf mehr als meine Handvoll Testdatensätze) das tut, was ich will.

Vielleicht sehe ich auch einfach den Wald vor lauter Bäumen nicht mehr.

Nochmal:
ich brauche von den Datensätzen, die eine bestimmte Bedingung (name='bla') erfüllen, den mit der dritt-jüngsten Startzeit.

Wie krieg ich das ohne das dreifach-Select-Monster hin?

vielen Dank im Voraus!
cu,
Andreas

[Vinzenz Mai]

Hallo Andreas,

gibt es eine Möglichkeit, mit einem select (natürlich mit order by, so daß eine Ordnung gegeben ist) die drittletzte Zeile der Ergebnis-Menge auszuwählen?

SELECT id,starttime FROM tabelle WHERE name='bla' ORDER BY starttime

Bei MySQL ginge das ja mit LIMIT, aber bei Oracle?

Mein naiver Ansatz mit ROWNUM = 3 im WHERE-Teil bringt ein leeres Ergebnis (Klar, durch diese Einschränkung hat die Ergebnismenge nur noch eine Zeile, und die hat ROWNUM = 1, ist also nicht die mit ROWNUM = 3, woraufhin die endgültige Ergebnismenge leer ist ...).

Wie krieg ich das ohne das dreifach-Select-Monster hin?

ein Subselect benötigst Du. Im inneren SELECT musst Du den jeweiligen Wert von ROWNUM in eine Spalte bekommen, dann kannst Du im äußeren SELECT auf den Wert 3 abfragen, siehe diese Archivdiskussion, die kein konkretes Statement liefert, aber den Weg anspricht. Es geht, hab' ich damals selbst durchgetestet.

Freundliche Grüße

Vinzenz

[dedlfix]

echo $begrüßung;

SELECT id, starttime FROM
  (
    SELECT id, starttime FROM
      (
        SELECT id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
      )
    WHERE ROWNUM < 4 ORDER BY starttime ASC
  )
WHERE ROWNUM < 2
Wie krieg ich das ohne das dreifach-Select-Monster hin?

Geht denn

SELECT * FROM
    (
      SELECT id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
    )
  WHERE ROWNUM = 3

nicht? Wenn ja, dann vielleicht so:

SELECT id, starttime FROM
    (
      SELECT ROWNUM as line, id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
    )
  WHERE line = 3

echo "$verabschiedung $name";

[dedlfix]

echo $begrüßung;

Nachdem ich nun http://www.muniqsoft.de/tipps/monatstipps/monattipps_2002.htm#September gefunden habe, gehe ich davon aus, dass

SELECT * FROM
    (
      SELECT id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
    )
  WHERE ROWNUM = 3

nicht gehen kann. Auch

SELECT id, starttime FROM
    (
      SELECT ROWNUM as line, id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
    )
  WHERE line = 3

wird nicht gehen. Es bleibt wohl doch nur ein dreifacher übrig, allerdings sollte man im mittleren die ROWNUM in einer Spalte ablegen und im äußeren diese Spalte auf 3 testen können. Was wohl auch einfacher zu durchschauen ist als ober- und unterhalb abzuschneiden.

echo "$verabschiedung $name";

[MudGuard]

Hi,

Geht denn
  SELECT * FROM
    (
      SELECT id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
    )
  WHERE ROWNUM = 3
nicht?

Nein, wie ich scon schrieb - da die Ergebnismenge des Selects bei ROWNUM = ... immer nur aus einer Zeile besteht, kann das nur funktionieren, wenn ROWNUM = 1 abgefragt wird.

SELECT id, starttime FROM
    (
      SELECT ROWNUM as line, id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
    )
  WHERE line = 3

Das ROWNUM wird - so sieht es für mich aus - vor dem ORDER BY eingefügt. Damit wird nicht die richtige Zeile selektiert ...

cu,
Andreas

[Vinzenz Mai]

Hallo Andreas,

gibt es eine Möglichkeit, mit einem select (natürlich mit order by, so daß eine Ordnung gegeben ist) die drittletzte Zeile der Ergebnis-Menge auszuwählen?

SELECT id,starttime FROM tabelle WHERE name='bla' ORDER BY starttime

gibt mir das wunderschön sortierte Ergebnis aus.
Allerdings alle Zeilen.

Bei MySQL ginge das ja mit LIMIT, aber bei Oracle?

ja, LIMIT wünsche ich mir bei anderen DBMSen ebenfalls, nicht nur bei Oracle.

Mit einem dreifach geschachtelten Select scheine ich das zu bekommen, was ich will:

SELECT id, starttime FROM
  (
    SELECT id, starttime FROM
      (
        SELECT id, starttime FROM tabelle WHERE name='bla' ORDER BY starttime DESC
      )
    WHERE ROWNUM < 4 ORDER BY starttime ASC
  )
WHERE ROWNUM < 2

ich brauche von den Datensätzen, die eine bestimmte Bedingung (name='bla') erfüllen, den mit der dritt-jüngsten Startzeit.
Wie krieg ich das ohne das dreifach-Select-Monster hin?

Hmm, ich sehe nicht, wie Du am Dreifach-Select vorbei kommst, allerdings könnte man es schöner (ok, ist Geschmackssache) schreiben:

Schritt für Schritt:

Meine Beispieldaten:

ID      WERT
----------------------
1       Beispiel
2       Text
3       Oracle
4       Informix
5       DB2
6       MS SQL Server
7       MySQL

SELECT  
    id,  
    wert  
FROM  
    beispiel  
ORDER BY  
    wert DESC;

liefert

ID      WERT
-----------------------
2       Text
3       Oracle
7       MySQL
6       MS SQL Server
4       Informix
5       DB2
1       Beispiel

Baue ich ROWNUM ein:

SELECT  
    ROWNUM line,  
    id,  
    wert  
FROM  
    beispiel  
ORDER BY  
    wert DESC;

so erhalte ich

LINE    ID WERT
-----------------------
3       2       Text
4       3       Oracle
1       7       MySQL
7       6       MS SQL Server
5       4       Informix
6       5       DB2
2       1       Beispiel

Klar, ORDER BY schlägt erst spät zu, somit ist ROWNUM hier unbrauchbar.
Selektieren wir also vom sortierten Ergebnis und fügen erst dort ROWNUM ein:

  
SELECT  
    ROWNUM line,  
    i.id,  
    i.wert  
FROM (  
    SELECT  
        id,  
        wert  
    FROM  
        beispiel  
    ORDER BY  
        wert DESC  
) i;  

liefert

LINE    ID      WERT
------------------------------
1       2       Text
2       3       Oracle
3       7       MySQL
4       6       MS SQL Server
5       4       Informix
6       5       DB2
7       1       Beispiel

Leider können wir nicht hier schon auf ROWNUM = 3 einschränken, denn dann erhalten wir

no data found

weil nur die Zeilen in der Ergebnismenge numeriert werden.
Es ist nur möglich, so wie Du es getan hast, auf ROWNUM < 4 einzuschränken, damit die Ergebnismenge möglichst klein wird. Und um diese Abfrage herum packen wir erneut ein SELECT:

  
SELECT  
    o.id,  
    o.wert  
FROM (  
    SELECT  
        ROWNUM line,  
        i.id,  
        i.wert  
    FROM (  
        SELECT  
            id,  
            wert  
        FROM  
            beispiel  
        ORDER BY  
            wert DESC  
    ) i  
    WHERE ROWNUM < 4    -- nur zur Optimierung  
                        -- Beachte: Ein Spaltenalias kannst Du hier nicht  
                        -- verwenden (zumindest in meiner Oracle 10g XE  
) o  
WHERE o.line = 3;       -- hier können wir problemlos auf Gleichheit prüfen  
                        -- Beachte: line ist hier *kein* Aliasname mehr  

liefert

ID      WERT
--------------
7       MySQL

Ob dies der effizienteste Weg unter Oracle ist, kann ich Dir nicht sagen.
Aber sie tut ganz sicher das, was sie soll - und das ist das, was Deine Anforderung ist.

Freundliche Grüße

Vinzenz

[MudGuard]

Hi,

ja, LIMIT wünsche ich mir bei anderen DBMSen ebenfalls, nicht nur bei Oracle.

Mir würde im Moment schon Oracle reichen ;-)

Hmm, ich sehe nicht, wie Du am Dreifach-Select vorbei kommst,

sch...

allerdings könnte man es schöner (ok, ist Geschmackssache) schreiben:

Naja, hier wird die Zwischenergebnismenge um eine Spalte aufgebläht.
Da bleib ich dann doch bei meiner Variante, direkt auf ROWNUM zu gehen.
Wird dann halt noch mit zig Zeilen Kommentar versehen (damit ersichtlich ist, warum da dreifach geschachtelt wird) in der Stored Procedure landen ...

cu,
Andreas

[Ilja]

yo,

gibt es eine Möglichkeit, mit einem select (natürlich mit order by, so daß eine Ordnung gegeben ist) die drittletzte Zeile der Ergebnis-Menge auszuwählen?

ja, natürlich kann oracle das auch, man muss nur wissen, wie ROWNUM funktioniert. das verwirrende daran ist, wann (zeitlich) ROWNUM vergeben wird, nämlich das ROWNUM -> vor <- der sortierung vergeben wird.

dann wird auch klar, warum rownum = 3 niemals ergebnise in oracle liefern wird rownum = 1, bzw. rownum <= 3 aber schon. das dbms nimt den ersten datensatz, gibt ihm ROWNUM 1 und prüft dann die WHERE klausel, die besagt nimm nur ROWNUM = 3, also schließt es ihn aus. nun nimmt das dbms den nächsten datensatz und gibt im natürlich wieder die ROWNUM 1 und die ist wieder ungleich 3......

das beste ist, die sortietnug mit DESC machen, ROWNUM mit aliasnamen dazu, das alles als unterabfrage in der FROM klausel und dann dort in der where klausel mit aliasname = 3 den datensatz auszuwählen.

SELECT t.zeile, t.spalte1, t.spalte2....
FROM (hier die unterabfrage mit rownum AS Zeile) t
WHERE t.zeile = 3
;

Ilja

[Vinzenz Mai]

Hallo Ilja,

gibt es eine Möglichkeit, mit einem select (natürlich mit order by, so daß eine Ordnung gegeben ist) die drittletzte Zeile der Ergebnis-Menge auszuwählen?

ja, natürlich kann oracle das auch, man muss nur wissen, wie ROWNUM funktioniert. das verwirrende daran ist, wann (zeitlich) ROWNUM vergeben wird, nämlich das ROWNUM -> vor <- der sortierung vergeben wird.

genau das bedingt ja das dreifach verschachtelte SELECT.

das beste ist, die sortietnug mit DESC machen, ROWNUM mit aliasnamen dazu,

wie Du meinen Einzelschritten und Deiner Anmerkung oben entnehmen kannst, führt das *nicht* zum gewünschten Ergebnis:

Damit ROWNUM den richtigen Datensatz numeriert, muss im innersten SELECT absteigend sortiert werden, ROWNUM kann erst in einem SELECT um diesen innersten sortierten SELECT angewandt werden

dann wird auch klar, warum rownum = 3 niemals ergebnise in oracle liefern wird rownum = 1, bzw. rownum <= 3 aber schon. das dbms nimt den ersten datensatz, gibt ihm ROWNUM 1 und prüft dann die WHERE klausel, die besagt nimm nur ROWNUM = 3, also schließt es ihn aus. nun nimmt das dbms den nächsten datensatz und gibt im natürlich wieder die ROWNUM 1 und die ist wieder ungleich 3......

aus diesem Grund muss um dieses Konstrukt noch ein weiteres SELECT außen herum gepackt werden. Erst in diesem ist die gezielte WHERE-Klausel möglich.

=> daraus folgt die Dreifachverschachtelung.

das alles als unterabfrage in der FROM klausel und dann dort in der where klausel mit aliasname = 3 den datensatz auszuwählen.

SELECT t.zeile, t.spalte1, t.spalte2....
FROM (hier die unterabfrage mit rownum AS Zeile) t
WHERE t.zeile = 3
;

Schau' es Dir selbst an. Verfolge Deine eigene Argumentation. Es liefert nicht das gewünschte Ergebnis. Hab' ich getestet.

Freundliche Grüße

Vinzenz

[Ilja]

yo Vinz und Andreas,

da habe ich mich ja wirklich selbst ausgetrickts, meine erklärung geliefert und dann anders gehandelt. also noch mal richtig, erst sortieren in der unterabfrage, dann ROWNUM mit alis namen vergeben und dann auf Zeile = 3 selektieren.... ;-)

Ilja

[MudGuard]

Hi,

gibt es eine Möglichkeit, mit einem select (natürlich mit order by, so daß eine Ordnung gegeben ist) die drittletzte Zeile der Ergebnis-Menge auszuwählen?

ja, natürlich kann oracle das auch, man muss nur wissen, wie ROWNUM funktioniert. das verwirrende daran ist, wann (zeitlich) ROWNUM vergeben wird, nämlich das ROWNUM -> vor <- der sortierung vergeben wird.

dann wird auch klar, warum rownum = 3 niemals ergebnise in oracle liefern wird rownum = 1, bzw. rownum <= 3 aber schon. das dbms nimt den ersten datensatz, gibt ihm ROWNUM 1 und prüft dann die WHERE klausel, die besagt nimm nur ROWNUM = 3, also schließt es ihn aus. nun nimmt das dbms den nächsten datensatz und gibt im natürlich wieder die ROWNUM 1 und die ist wieder ungleich 3......

das beste ist, die sortietnug mit DESC machen, ROWNUM mit aliasnamen dazu, das alles als unterabfrage in der FROM klausel und dann dort in der where klausel mit aliasname = 3 den datensatz auszuwählen.

Das geht deswegen nicht, weil das Einfügen der ROWNUM _VOR_ dem ORDER BY geschieht ...

cu,
Andreas