Verschachtelte Anfrage

[shorty87]

Hallo!

Ich habe folgendes Problem:

Ich habe eine Tabelle "action" mit den Attributen aId (primary key), uId, sId, name und time (timestamp). aId ist einmalig, aber name kommt sehr oft doppelt vor.

Die Anfrage, die ich in SQL stellen möchte, wäre:

In "action" alle Elemente durchgehen, die uId = '20' (also ein fester Wert) haben; nennen wir das aktuelle Element "a1"
alle Elemente in derselben Tabelle finden, die denselben Namen haben wie a1, wir erhalten eine Liste von Elementen, nennen wir sie "la"
a1.time muss der Maximalwert aller Werte der Spalte time aus la sein

Ich möchte genau diese a1-Elemente zurückgeben. Also soll von jedem "name", dass es gibt, das mit dem größten "time"-Wert zurückgegeben werden.

Ich habe nun so einiges ausprobiert mit CASE, IF, IN, Verschachtelungen. Aber leider hat noch nichts funktioniert.

Ich würde mich wirklich sehr freuen, wenn jemand eine Lösung finden würde!

Vielen Dank!

[CLL]

also erstmal willst du alle Namen mit uid=20?

select distinct name from action where uid='20'

und von dieser Liste ausgehend die Einträge mit gleichem Namen und höchstem Zeitstempel?

select name, max(time) from action where name in (select distinct name from action where uid='20') group by name;

[shorty87]

Erst einmal danke! es kommt dem Ganzen schon sehr viel näher!

Das Problem ist, ich muss nicht nur den Namen wissen, sondern brauche die ganze Zeile aus der Action Tabelle, weil ich genau diese Elemente noch weiterverarbeiten muss.

Also ich möchte alle Zeilen, die in der Spalte "uid" den Wert 20 haben und von denen sollen die Zeilen ausgewählt werden, so dass jeder Name, den es gibt einmal vorkommt, aber mit der Zeile in der es den spätesten/aktuellsten Timestamp hat.

Z.B. habe ich in der Tabelle
aId (primary key)|| uId || sId || name || time
1 || 20 || 5 || going || 15.05.2012
2 || 20 || 9 || reading || 30.06.2012
3 || 20 || 9 || reading || 07.08.2012

Dann sollen die Zeilen 1 und 3 zurückgegeben werden. Zeile 1, weil es für "going" keinen aktuelleren Wert gibt und Zeile 3, weil es der aktuellste Wert für "reading" ist.

Mit "max(time)" erhalte ich zwar den höchsten "time"-Wert, aber wenn ich bei select noch aId angebe, bekomme ich die aId für den ersten Eintrag mit der Time des letzten..

[ebis]

Das Problem ist, ich muss nicht nur den Namen wissen, sondern brauche die ganze Zeile aus der Action Tabelle, weil ich genau diese Elemente noch weiterverarbeiten muss.

selektiere Max (Time) gruppiert nach Deinen Kritierien und joine das Ergebnis mit Deiner Tabelle
[HIGHLIGHT="SQL"]WITH Data (aId , uId , sId , name ,time)
as ( SELECT 1, 20, 5, 'going', CAST ('15.05.2012' AS DATE) UNION ALL
SELECT 2, 20, 9, 'reading', CAST ('30.06.2012' AS DATE) UNION ALL
SELECT 3, 20, 9, 'reading', CAST ('07.08.2012' AS DATE)
)
SELECT D.*
FROM Data AS D
JOIN (SELECT MAX(TIME) AS Time
, Name
, UID
FROM Data
GROUP BY Name, UID
) AS X ON D.uId = X.uId
AND D.name = X. name
ANd D.time = X.Time[/HIGHLIGHT]

aId         uId         sId         name    TIME
----------- ----------- ----------- ------- ----------
1           20          5           going   2012-05-15
3           20          9           reading 2012-08-07

PS: WITH-Statement dient hier nur zum erstellen einer virtuellen Tabelle (Ersetze Data durch Deine Tabelle)

[shorty87]

Danke, aber das hat gar nicht geklappt. Erstmal gibt es ja uid und aid, wovon aid der primary key ist und uid ein fester wert sein muss. hab deine anfrage mal angepasst, aber ich bekomme dann ganz viele elemente, statt nur das aktuellste zu jedem "namen"

[ebis]

Danke, aber das hat gar nicht geklappt. Erstmal gibt es ja uid und aid, wovon aid der primary key ist und uid ein fester wert sein muss. hab deine anfrage mal angepasst, aber ich bekomme dann ganz viele elemente, statt nur das aktuellste zu jedem "namen"

Ich habe mal das Ergebnis zu Deinen Beispieldaten hinzugefügt...
Und zweitens habe ich keine Lust herumzuraten, was Du da angepasst hast...

[Falk Prüfer]

Hallo,
siehe auch diesen Thread: Bei "doppelten" Datensätzen nur einen anzeigen für Lösungsansätze.

Gruß Falk

[SiegfriedEhlert]

Subselect

Hallo shorty87,

die Lösung ist ein simples Subselect:

CREATE Table ACTION as
    SELECT 1 a, 20 u,  5 s, 'going' n,    to_date('15.05.2012', 'DD.MM.YYYY') t from Dual
    Union All
    SELECT 2 a, 20 u,  9 s, 'reading' n,  to_date('30.06.2012', 'DD.MM.YYYY') t from Dual
    Union All
    SELECT 3 a, 20 u,  9 s, 'reading' n,  to_date('07.08.2012', 'DD.MM.YYYY') t from Dual
/
Table created.

SELECT a "aId", u "uId", s "sId", n "Name", t "Time"
from   ACTION a0
where  u = 20
and    t = ( 
           SELECT max(t) 
           from   ACTION a1
           where  a1.u = a0.u
           and    a1.n = a0.n
       )   
/

       aId        uId        sId Name       Time    
---------- ---------- ---------- ---------- --------
         1         20          5 going      15.05.12
         3         20          9 reading    07.08.12

2 rows selected.

Ich habe die Attribute umbenannt, da Oracle "SID" als Attributname nicht akzeptiert.

Siegfried

[shorty87]

Danke für eure Antworten!
@ Siegfried: Leider habe ich ganz viele Elemente zurück bekommen und nicht nur das aktuellste

Dafür habe ich den anderen Thread befolgt und es hat endlich funktioniert!!

SELECT MAX(a.aId), a.uId, a.sId, a.name, a.time
FROM action a
WHERE a.time = (
        SELECT MAX(a2.time)
        FROM action a2
        WHERE a2.name = a.name
        AND a.uId = '20' )
GROUP BY a.name, a.time

Jetzt habe ich aber noch ein weiteres Problem: Ich habe eine weitere Tabelle Ich habe sie vorher nicht erwähnt, weil ich dachte, es wäre mit einem einfachen Join realisierbar. Ist es aber nicht

Also die zweite Tabelle heißt info und hat die Spalten aId (aus action), iId (der eigene Primärschlüssel), type, title, value.
Zu einem action Element, können mehrere info Elemente gehören.

Mein Ziel war es eine Anfrage zu stellen, wo ich die Spalten beider Tabellen bekomme, also a.* und i.*
Bisher hatte ich einfach die Bedingung action.aId = info.aId gestellt und zu jedem action Element alle dazugehörigen info Elemente aufgelistet bekommen.
Aber wenn ich nun folgendes mache:

SELECT MAX(a.aId), a.uId, a.sId, a.name, a.time, i.aId, i.iId, i.title, i.type, i.`value`
FROM action a, info i
WHERE a.time = (
        SELECT MAX(a2.time)
        FROM action a2
        WHERE a2.name = a.name
        AND a2.uId = '20')
AND a.aId = i.aId 
GROUP BY a.name, a.time

bekomme ich nur ein info Element zu jedem action Element statt alle zugehörigen. Außerdem habe ich extra i.aId ausgeben lassen, um zu kontrollieren, ob es wenigstens das richtige info Element ist. Ist es leider nicht. Also es wird ein info Element zurückgegeben, dass nicht zu dem action Element gehört, in dessen Zeile es erscheint, was ich überhaupt nicht verstehe.
Wenn ich die GROUP BY-Zeile ändere

GROUP BY a.name, a.time, i.title

Dann stimmt nur in einer Zeile das info Element zu dem action Element. In den restlichen Zeilen stehen einfach "falsche" info Elemente, deren aId nicht passt. Wie ist das überhaupt möglich?

[Falk Prüfer]

Deine "undefinierbaren" Ergebnisse bekommst du, weil deine Gruppierung ziemlich sinnfrei ist. Nur MySQL lässt solche Konstrukte überhaupt zu und legt damit die Verantwortung in die Hand des Programmierers.
In der GROUP-BY-Klausel müssen ALLE Spalten aufgeführt werden, die auch in der SELECT-Klausel stehen. Ausgenommen davon sind aggregierte und funktional abhängige Felder. Hält man sich nicht an diese Regel, bekommt man Ergebnisse die nicht interpretierbar sind - wie du ja gemerkt hast. Für meine Auffassung ist die Gruppierung sogar völlig Überflüssig.
Auch fehlt für mich eine Where-Klausel mit der Einschränckung der uId = 20. Diese hast du zwar im Subselect, aber sind die Datensätze über time wirklich eindeutig zuordenbar? Und warum überprüfst du einen numerischen Wert (20) als String (a.uId = '20')?
[highlight=sql]
SELECT a.aId, a.uId, a.sId, a.name, a.time, i.aId, i.iId, i.title, i.type, i.`value`
FROM action a
inner join info i on i.aId = a.aId
WHERE a.time = (
SELECT MAX(a2.time)
FROM action a2
WHERE a2.name = a.name
AND a2.uId = 20)
AND a.uId = 20
[/highlight]

Gruß Falk

[SiegfriedEhlert]

Hallo shorty87,

dass du ganz viele Datensätze zurück bekommen hast, liegt wahrscheinlich in der Natur der Daten: es gibt mehrere Datensätze mit "uId = 20" und gleichem Namen und gleichem max(time).

aId  uId  sId  name       time
1    20   5    'going'    15.05.2012
2    20   9    'reading'  30.06.2012
3    20   9    'reading'  07.08.2012
4    20   9    'reading'  07.08.2012

Deine vorgestellte Lösung

SELECT MAX(a.aId), a.uId, a.sId, a.name, a.time
FROM action a
WHERE a.time = (
        SELECT MAX(a2.time)
        FROM action a2
        WHERE a2.name = a.name
        AND a.uId = '20' )
GROUP BY a.name, a.time

ist syntaktisch fehlerhaft

with action( "aId", "uId", "sId", "name", "time") as(
    SELECT 1, 20, 5, 'going', to_date('15.05.2012','DD.MM.YYYY') from dual
    union all
    SELECT 2, 20, 9, 'reading', to_date('20.06.2012','DD.MM.YYYY') from dual
    union all
    SELECT 3, 20, 9, 'reading', to_date('07.08.2012','DD.MM.YYYY') from dual
)
SELECT max(a."aId"), a."uId", a."sId", a."name", a."time"
from   action a
where  a."time" = (
         SELECT max(a2."time")
         from   action a2
         where  a2."name" = a."name"
         and    a."uId" = '20'
       )
group by a."name", a."time"
                     *
Error at line 8
ORA-00979: Kein GROUP BY-Ausdruck

und entspricht definitiv nicht deinen formulierten Anforderungen.

Siegfried