[SQL] Eine bestimmte Zeile ausgeben

[Whiz-zarD]

Moin.

Ich habe ein kleines Problem:

In einer SQL Übungsaufgabe soll ich die Zeile ausgeben, die in einer Spalte den fünftgrößten Wert hat. Als Server läuft Oracle Database.
Leider unterstützt der Server nicht alle Befehle, die man auch von MySQL kennt und somit gibt es auch kein Limit-Befehl.

Die Aufgabe selbst soll mit einer Unterabfrage gelöst werden. Ich steh da irgendwie auf dem Schlauch. Kann da vielleicht jemand ein Tipp geben?

[dead_orc]

Moin.

Ich habe ein kleines Problem:

In einer SQL Übungsaufgabe soll ich die Zeile ausgeben, die in einer Spalte den fünftgrößten Wert hat. Als Server läuft Oracle Database.
Leider unterstützt der Server nicht alle Befehle, die man auch von MySQL kennt und somit gibt es auch kein Limit-Befehl.

Die Aufgabe selbst soll mit einer Unterabfrage gelöst werden. Ich steh da irgendwie auf dem Schlauch. Kann da vielleicht jemand ein Tipp geben?

Ich hätte da auch aufm Schlauch gestanden, aber weil mich das gerade interessiert hat, hab ich mal gegooglet und bin erst auf diesen Blogeintrag (http://www.fladi.de/2009/11/13/mysql-limit-funktion-mit-oracle-und-php-fuer-jqgrid/) und später auf diesen Artikel von Oracle (http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html) gestoßen. Solltest du dir nicht gleich die Lösung auf dem Silbertablett präsentieren lassen wollen: Der Trick heißt wohl ROWNUM. So wie ich das verstehe, ginge das dann so:


SELECT * FROM (SELECT * FROM tab ORDER BY col DESC) WHERE ROWNUM=5

[Irian]

Sollte eigentlich auch ohne solche Spezialkonstrukte gehen (kann ich jetzt für Oracle natürlich nicht garantieren).

Überleg mal, was du suchst, ist die Zeile x, wo die Anzahl der Zeilen, bei denen ein bestimmter Wert kleiner ist als der der Zeile x, 4 ist.

Kleiner Hint: Die Anzahl zählt man mit... COUNT

Mal die Lösung, die ich spontan auf meiner WebSeiten-Datenbank gemacht habe (Tabelle heißt hier "page", die Spalte, nach der verglichen wird, ist die page_id):
SELECT * FROM page p WHERE (SELECT count(*) FROM page q WHERE q.page_id < p.page_id) = 4;

(Wie schon geschrieben: Selektiere die Zeile, wo die Anzahl der Zeilen, bei denen die page_id kleienr ist, gleich 4 ist, was dann natürlich die 5. Zeile ist)

[Drakes]

Irian, deine Query ist keine Lösung zum Problem, da die Werte scheinbar nicht geordnet sein müssen.

[Irian]

Ich glaube, du irrst dich. Meine Lösung gibt die Zeile aus, für die es 4 andere Zeilen gibt, wo der Wert einer bestimmten Spalte kleiner ist. Also ist das die Zeile...


die in einer Spalte den fünftgrößten Wert hat

Ich sehe nicht, warum die Sortierung eine Rolle spielen sollte. Es geht um den fünft-größten Wert, ganz egal ob der in Zeile 5 oder 123 steht.

Edit/Hinweis: Ich glaube, du hast was nicht verstanden: PAGE_ID ist ein BEISPIEL. Es geht bei meinem Query NICHT darum, die ID zu selektieren. Statt Page_ID könnte da auch "Preis", "Hausnummer", "Datum" oder sonstwas stehen. Das ist einfach die Spalte, wo der Wert steht. Mein Query hängt NICHT von einem Schlüssel ab. Ich hab nur fürs Beispiel auf die Schnelle eine Spalte genommen, wo numerische Werte standen. Ich hätte da auch "date" schreiben können.
Mein Beispiel selektiert also die Zeile mit der fünftgrößten page_id. Aber natürlich kann man damit auch die Zeile mit der fünftgrößten Körpergröße selektieren. Oder der Nasenlänge. oder sonstwas :-)

[Kyuu]

SELECT * FROM page p WHERE (SELECT count(*) FROM page q WHERE q.page_id < p.page_id) = 4;



Ist das nicht O(N^2)?

Ich hätte noch diese Lösung anzubieten:



SELECT col1, col2, ...
FROM (SELECT col1, col2, ..., ROWNUM rn
FROM (SELECT *
FROM tab
ORDER BY coln DESC)
WHERE ROWNUM < 6 )
WHERE rn = 5;


Das sollte für große Datenbanken schneller sein, wenn ich richtig liege, ist aber weniger elegant, da die Kolumnen explizit angegeben werden müssen. Was es macht ist folgendes:

1. Wähle alles aus tab und sortiere absteigend nach coln.
2. Wähle die ersten 5 Reihen.
3. Wähle die 5. Reihe.

[Drakes]

Tut mir Leid, hast natürlich recht, hab die Query zuerst falsch gelesen.

@Kyuu: Ohne genau zu wissen, wie es die Datenbank intern macht, würde ich auch diese Laufzeitkomplexität abschätzen.

[Irian]

Jo, die Abschätzung ist rein theoretisch korrekt - aber rein praktisch gesehen relativ sinnfrei, da in modernen Datenbanken viele Optimierungen ablaufen. Ohne genaue Kenntnis der Datenbank dürfte es schwer sein, realistisch abzuschätzen, was da wirklich rauskommt. Dass meine Lösung aber aller Wahrscheinlichkeit nach nicht die performanteste sein dürfte, ist wohl jedem klar - dafür ist sie halt eher Datenbank-unabhängig :-)

[Kyuu]

Ich würde sie nicht als sinnfrei bezeichnen. Dass Optimierungen durchgeführt werden ist Fakt, ob jedoch eine Optimierung in dem konkreten Fall stattfindet, so dass die oberflächlich festgestellte O(N^2)-Abschätzung falsch ist, ist alles andere als Fakt und kann ohne genaue Kenntnisse über die Implementierung genauso wenig ausgesagt werden, wie, dass O(N^2) absolut korrekt ist. In jedem Fall ist es eine gute Möglichkeit um mehrere Algorithmen schnell vergleichen zu können, falls es überhaupt auf die Performance ankommt. Genaue (lies: konkrete) Aussagen kann man sowieso nur durch Profiling treffen.

[Whiz-zarD]

Jo, die Abschätzung ist rein theoretisch korrekt - aber rein praktisch gesehen relativ sinnfrei, da in modernen Datenbanken viele Optimierungen ablaufen. Ohne genaue Kenntnis der Datenbank dürfte es schwer sein, realistisch abzuschätzen, was da wirklich rauskommt. Dass meine Lösung aber aller Wahrscheinlichkeit nach nicht die performanteste sein dürfte, ist wohl jedem klar - dafür ist sie halt eher Datenbank-unabhängig :-)

Deine ist weder die performanteste noch die Korrekte, da sie überhaupt keinen Wert zurückliefert ;)
Das kann schonmal nicht funktionieren, weil zuerst die inneren Abfragen bearbeitet werden und was soll denn da rauskommen, da er in der inneren auch die äußere benötigt, die noch nicht existiert?

Ein weiteres Problem ist rownum, da rownum nicht die Nummerierung der ausgegebenen Zeilen anzeigt, sondern die Nummerierung, die intern in der Datenbank verwendet wird. (Zumindest scheint das bei Oracle so zu sein) Meine Zeile, die ich ausgeben soll, hat nämlich den Wert 52 und nicht 5.

Auf die Idee mit rownum kam ich auch schon aber es klappt einfach nicht.

Dennoch danke schonmal für die Antworten :)

[Drakes]

Wirklich?
http://www.java2s.com/Code/Oracle/Subquery/Selectfromvirtualtablesubquerywithrownum.htm
Sagt da etwas anderes :confused:

[Kyuu]

@Whiz-zarD:

Also ich habe beide Lösungen mit Oracle 10g getestet und beide funktionieren, wobei ich bei Irians noch das '<' durch '>' ersetzen musste, da sonst der fünftkleinste Wert, statt des fünftgrößten ausgegeben wurde. ^^

Und dead_orcs Code (edit: so wie Gekigangers) wird aus dem Grund nicht funktionieren, weil ROWNUM in dem Kontext nicht auf Gleichheit geprüft werden kann, steht auch in dem Oracle-Link, den er gepostet hat.

[Gekiganger]

select wert
from
(
select wert
from tabelle
group by wert
order by wert desc
)
where rownum = 5;

Dubletten entfernen nicht vergessen.

Und die Aufgabe kann auch nur mit einer Unterabfrage gelöst werden. Warum, steht im Oracle Tutorial.

[Irian]

Deine ist weder die performanteste noch die Korrekte, da sie überhaupt keinen Wert zurückliefert

Das ist, kurz gesagt, Unsinn. Natürlich habe mein Query getestet und das Ergebnis war völlig korrekt. Du argumentierst also quasi an der Realität vorbei...


Das kann schonmal nicht funktionieren, weil zuerst die inneren Abfragen bearbeitet werden und was soll denn da rauskommen, da er in der inneren auch die äußere benötigt, die noch nicht existiert?

Du solltest dein Wissen über SQL bitte nochmal etwas aufbessern, bevor du solche Beinahe-Wahrheiten verkündest. Anders gesagt: Du irrst dich. Such mal nach "correlated subquery".


Also ich habe beide Lösungen mit Oracle 10g getestet und beide funktionieren, wobei ich bei Irians noch das '<' durch '>' ersetzen musste, da sonst der fünftkleinste Wert, statt des fünftgrößten ausgegeben wurde. ^^

Groß... Klein... Details! Anders gesagt: Ups, stimmt natürlich :-)


Dubletten entfernen nicht vergessen.

Hier sehe ich sogar einen Vorteil meines Ansatzes, der würde ALLE Zeilen ausgeben, die den fünftgrößten Wert beinhalten. Praktisch :-)
(Edit: Ginge natürlich andersrum auch, einfach höchsten Wert suchen und dann alle Zeilen damit ausgeben, vermutlich sogar schneller... Wie gesagt, dieses innere Query hier dürfte kaum die schnellste Lösung sein, da müßte man sich die Logs mal anschauen).

[Whiz-zarD]

Das ist, kurz gesagt, Unsinn. Natürlich habe mein Query getestet und das Ergebnis war völlig korrekt. Du argumentierst also quasi an der Realität vorbei...
aha. Dann redet der SQL Developer von Oracle an der Realität vorbei?
Ich sage hier nur, was ich hier sehe und rownum hat den Wert 52, bei der Zeile, die ich ausgeben will.

http://www.npshare.de/files/938d9468/sql.PNG

[Kyuu]

Zeig deinen Code und ich zeige dir wo dein Fehler ist. (Nicht dass ich nicht schon einen korrekten Lösungsvorschlag mit ROWNUM gepostet habe.)

[Whiz-zarD]

Zeig deinen Code und ich zeige dir wo dein Fehler ist.



select *
from studienleistung
where ects is not null
order by ects desc;


Das ist jetzt die Innere. Wenn ich jetzt select *, rownum schreibe, meldet er sofort einen Fehler.
Die äußere kann ich machen, was ich will. Nach den Methoden, die hier beschrieben werden, bekomm ich ebenfalls einen Fehler. Ansonsten ist und bleibt rownum = 52.

edit:
Nach deiner Methode


select *
from studienleistung s
where ( select count(*)
from studienleistung d
where s.ects < d.ects) = 4;


gibt er nicht mal ein ein Ergebnis aus.

Ausserdem frag ich mich die ganze Zeit, was SQL großartig mit Webentwicklung zu tun hat. http://www.multimediaxis.de/images/smilies/old/1/gruebel.gif
Sicherlich werden die Daten in eine SQL Tabelle gespeichert aber es gibt noch andere Anwendungsbereiche für SQL ausser Webentwicklung.

[Drakes]

Und du hast
SELECT ects FROM (SELECT ects FROM studienleistung ORDER BY ects DESC) WHERE ROWNUM=5 wirklich schon ausprobiert?

[Whiz-zarD]

Und du hast
SELECT ects FROM (SELECT ects FROM studienleistung ORDER BY ects DESC) WHERE ROWNUM=5 wirklich schon ausprobiert?

Zwar keine Fehlermeldung aber auch keine Ausgabe.

[Kyuu]

select *
from studienleistung
where ects is not null
order by ects desc;


Das ist jetzt die Innere. Wenn ich jetzt select *, rownum schreibe, meldet er sofort einen Fehler.
Die äußere kann ich machen, was ich will. Nach den Methoden, die hier beschrieben werden, bekomm ich ebenfalls einen Fehler. Ansonsten ist und bleibt rownum = 52.


Ich brauche schon die gesamte Abfrage, nicht nur die innere.

select *, rownum ist falsch, denn entweder du willst implizit alle, oder du gibst die Spalten, die du brauchst explizit an.

Außerdem musst du zuerst ordnen, bevor du ROWNUM benutzt, da ROWNUM sonst auf die ungeordneten Einträge angewendet wird und danach nach ECTS geordnet wird.

Ohne jetzt deine ganzen Bezeichnungen genau zu kennen, sollte deine Abfrage wie folgt aufgebaut sein:

1. Die innerste Abfrage wählt die Tabelle studienleistung und ordnet die Einträge nach ECTS:



select *
from studienleistung
where ects is not null
order by ects desc;


2. Aus dem Ergebnis der innersten Abfrage (also den geordneten Einträgen) wählt die nächstäußere Abfrage die ersten N Einträge mit Hilfe von ROWNUM:



select ects, ROWNUM rn
from (ErgebnisDerInnerstenAbfrage)
where ROWNUM <= N


Wichtig hier, dass die ROWNUM-Werte, die hier rauskommen in der nächstäußeren Abfrage verfügbar sind, also zwischenspeichern in rn.

3. Nun wählt die äußerste Abfrage aus dem Ergebnis der vorherigen Abfrage (also den ersten N Einträgen der nach ECTS geordneten Einträge aus studienleistung) den untersten Eintrag, also den N-ten Eintrag und zwar mit Hilfe des zuvor in rn gespeicherten ROWNUM-Wertes:



select ects
from (ErgebnisDerMittlerenAbfrage)
where rn = N;


Hier können wir ruhig auf Gleichheit der ROWNUM-Werte prüfen, da sie in einer Spalte zwischengespeichert sind.

Wenn du nun alle Abfragen in einander verschachtelst und die korrekten Bezeichnungen verwendest, sollte es funktionieren.



edit:
Nach deiner Methode


select *
from studienleistung s
where ( select count(*)
from studienleistung d
where s.ects < d.ects) = 4;


gibt er nicht mal ein ein Ergebnis aus.


Das ist Irians Ansatz, aber es funktioniert definitiv auch. Unter diesen Umständen: Welche Oracle-Version benutzt du?


Und du hast
SELECT ects FROM (SELECT ects FROM studienleistung ORDER BY ects DESC) WHERE ROWNUM=5 wirklich schon ausprobiert?

Ich habe doch bereits geschrieben, dass es nicht funktionieren wird. ^^

[Irian]

Dass SQL nicht nur was mit Webentwicklung zu tun hat, dürfte den meisten Leuten klar sein, denke ich. *schulterzuck*

Zu deinem Problem: Du hast zum Teil recht (was die spezielle Abfrage angeht) und ich hab recht (was das Allgemeine über SQL angeht, das mit den inneren Queries hast du imho noch etwas missverstanden). Tatsächlich macht meine Abfrage Zicken, wenn die Situationen sehr speziell sind.

Ich hab mal ne Testtabelle "test" angelegt mit 10 (ids 1-10) Zeilen mit ner Spalte "wert". Das Query ist also:


SELECT * FROM test t1 WHERE ( SELECT COUNT( * ) FROM test t2 WHERE t1.wert > t2.wert ) = 4;

Beispiel 1: Die 10 Zeilen haben die Werte 10 bis 100:

Dann ist das Ergebnis korrekterweise 5, 50

Beispiel 2: Alle Zeilen haben den Wert 10

Kein Ergebnis (0 Zeilen)

Beispiel 2: Die Zeilen haben die Werte 10,10,10,20,20,30,40,50,60,70

Kein Ergebnis (0 Zeilen)

Das kommt daher, dass er genau 4 Zeilen finden will, die kleiner sind, in all diesen Fällen gibts aber mehr als 4 für die richtige Zeile. Einfach >= setzen geht auch nicht, weil er dann ALLE Zeilen ausgibt die größer sind (und wenn wir Limit hätten, hätten wir das Problem ja nicht).

Die folgende Query könnte etwas besser sein, langsam wirs aber reichlich lächerlich (und in der Arbeit hätte ich auch schon lange zu nem Datenbank-spezifischen Befehl gegriffen *g*)...


SELECT * FROM test t1 WHERE ( SELECT COUNT( * ) FROM (SELECT * FROM test t3 group by t3.wert) t2 WHERE t1.wert > t2.wert ) = 4;

[Whiz-zarD]

Es sieht nun so aus:


SELECT ects
FROM (SELECT ects, ROWNUM rn
FROM (SELECT *
FROM studienleistung
WHERE ects IS NOT NULL
ORDER BY ects DESC;)
WHERE ROWNUM <= N;)
WHERE rn = N;


Da meldet er den Fehler "Rechte Klammer fehlt". In der Zeile, wo WHERE ROWNUM <= N;) steht aber ich wüsste nicht, wo da eine Klammer fehlen sollte.



Das ist Irians Ansatz, aber es funktioniert definitiv auch. Unter diesen Umständen: Welche Oracle-Version benutzt du?

Oracle® Database Express Edition 10g Release 2 (10.2)
Ist halt die Version, die auch intern in der Schule verwendet wird und die sollen wir auch benutzen.

[Kyuu]

Mach einfach die Semikola in den inneren Abfragen raus und vergiss nicht N durch ein Literal zu ersetzen.

[Whiz-zarD]

Er gibt zwar nun ein Ergebnis aus aber nicht das richtige.
Das Problem ist, dass einige Zahlen, in der "ects" Spalte, mehrmals vorkommen und mit select distinct klappt es auch nicht.
Ausserdem soll ich ja alles ausgeben, was zur Spalte gehört und nicht nur die Zahl alleine

[Kyuu]

Er gibt zwar nun ein Ergebnis aus aber nicht das richtige.
Das Problem ist, dass einige Zahlen, in der "ects" Spalte, mehrmals vorkommen


Ich verstehe nicht, wieso es das falsche Ergebnis bei dir ausgeben soll. Es gibt genau die Zeile aus, die in der geordneten Tabelle an der Stelle N vorkommt. Ob da mehrere Zeilen mit einer ECTS-Zahl gibt, oder nicht, spielt keine Rolle und welche von denen nun gewählt wird, hängt von dem Sortieralgorithmus der Oracle-Datenbank ab, sowie von ihren ursprünglichen Positionen.



Ausserdem soll ich ja alles ausgeben, was zur Spalte gehört und nicht nur die Zahl alleine

Deswegen habe ich auch ursprünglich col1, col2, ... geschrieben, du musst also alle Kolumnen, die du angezeigt haben willst, explizit angeben. Unbequem, aber ich kenne keinen anderen Weg.

Edit: Achso, du wolltest also doch alle Vorkommnisse haben, so wie in Irians Ansatz, sowie nur distinkte ECTS-Zahlen in den inneren Auswahlen berücksichtigen.

[Whiz-zarD]

So, habs nun endlich gelöst :)
Danke an alle ^^

Hier die Lösung:



select *
from studienleistung
where ects = ( SELECT ects
FROM (SELECT ects, ROWNUM rn
FROM (SELECT distinct ects
FROM studienleistung
WHERE ects IS NOT NULL
ORDER BY ects DESC)
WHERE ROWNUM <= 5)
WHERE rn = 5)
;