MySQL - Doppelte Einträge filtern

[Martin Jakobi]

Hallo,

ich weiß, dass dieses Thema nun schon mehrfach aufkam. Nach langem Googlen hat bei mir jedoch kein Ergebnis funktioniert.

Zu meinem Problem:
Ich nutze MySQL - 5.0.32

Und habe drei Tabellen wo folgende Spalten für den Fall interessant sind:

Tabelle stream
stream_id (INT, Primary, Index)
user_id (INT)
type_id (INT)
linux_timestamp (INT)

Beispielinhalt stream:
1 | 230 | 2 | 1268615468
2 | 180 | 2 | 1268615483
3 | 230 | 1 | 1268615504
4 | 230 | 3 | 1268615555
5 | 320 | 1 | 1268615567

Tabelle benutzer
user_id (INT, Primary, Index)
nickname (varchar)
vorname (varchar)
vornameview (BOOL/SMALLINT)

Beispielinhalt benutzer
180 | martin | Martin  | 1
230 | theo   | Theodor | 1
320 | jonas  | Jonas   | 1

Tabelle type
type_id (INT, Primary, Index)
type_text (varchar)
icon (varchar)

Beispielinhalt type:
1 | Egal | Egal
2 | Egal | Egal
3 | Egal | Egal

Nun möchte ich mit einer Abfrage wie der gleich folgenden erreichen, dass mir die (in diesem Beispiel) zwei neuesten Einträge aus der Tabelle stream angezeigt werden. Dabei soll nur ein Eintrag pro User angezeigt werden. In meinem Beispiel in der Tabelle stream sollen also nur Eintrag 5 und 4 ausgegeben werden (also die neuesten des jeweiligen Users).

Nun liest man überall "benutze GROUP BY oder DISTINCT". Allerdings wenn ich Group by benutze, lässt er einfach Daten weg. In meinem Beispiel der Tabelle stream werden dann Daten von dem User mit der ID 230 und 180 ausgegeben, nicht jedoch von dem User 320 (Eintrag 5).

Möglicherweise verstehe ich die Funktion GROUP BY nicht richtig. Habt ihr eine Lösung für mich?

Hier der Code, welchen ich zurzeit verwende (auf die Beispiele gemünzt):

SELECT ta.*, tt.type_text, tt.icon, tu.nickname, tu.vorname, tu.vornameview  
FROM stream AS ta, type AS tt, benutzer AS tu  
WHERE ta.type_id = tt.type_id AND ta.user_id = tu.user_id  
GROUP BY ta.user_id  
ORDER BY ta.linux_timestamp DESC  
LIMIT 2

Liebe Grüße
Martin

[Martin Jakobi]

Ps. natürlich sind die Tabellennamen MySQL-Konform (bspw. heißt die Tabelle "type" in wirklichkeit nicht "type").

[Martin Jakobi]

Ich denke viele klicken das Thema gar nicht erst an, da es schon so oft vorkam. Ich würde euch jedoch bitten einmal kurz einen Blick über meine Problematik zu werfen, da diese sich meiner Meinung nach von den anderen Fällen unterscheidet.

Liebe Grüße
Martin

[Vinzenz Mai]

Hallo Martin,

Ich denke viele klicken das Thema gar nicht erst an, da es schon so oft vorkam.

die Lösung zu Deinem Problem ist es, sich von GROUP BY zu lösen.
Du suchst eine korrelierte Unterabfrage. Schauen wir es uns an Deinem Beispiel an:

Tabelle stream:

stream_id   user_id     type_id     linux_timestamp
----------- ----------- ----------- ---------------
1           230         2           1268615468
2           180         2           1268615483
3           230         1           1268615504
4           230         3           1268615555
5           320         1           1268615567

Tabelle benutzer

user_id     nickname   vorname    vornameview
----------- ---------- ---------- -----------
180         martin     Martin     1
230         theo       Theodor    1
320         jonas      Jonas      1

Tabelle type (mit leicht modifizierten Inhalten)

type_id     type_text  icon
----------- ---------- ----------
1           Text 1     Icon 1
2           Text 2     Icon 2
3           Text 3     Icon 3

Du suchst zunächst die zwei neuesten Einträge in stream unter der Randbedingung, dass sie von unterschiedlichen Benutzern stammen. Die user_id und den zugehörigen linux_timestamp könntest Du zwar über GROUP BY mit Hilfe der Aggregatsfunktion MAX() bestimmen, nicht jedoch den zugehörigen Eintrag in der Spalte type_id. Dazu benötigst Du - wie bereits erwähnt - eine korrelierte Unterabfrage:

Wir beschaffen uns im ersten Schritt die neuesten Einträge *aller* Benutzer und sortieren die Ergebnismenge absteigend nach dem Eintragszeitpunkt, um die neuesten als erste zu erhalten:

SELECT                            -- Gib mir die Inhalte der Spalten  
    s1.stream_id,                 -- stream_id  
    s1.user_id,                   -- user_id  
    s1.type_id,                   -- type_id  
    s1.linux_timestamp,           -- und Timestamp  
FROM                              -- aus  
    stream s1                     -- der Tabelle stream, die unter dem  
                                  -- Namen s1 angesprochen wird.  
                                  -- Der Aliasname ist notwendig, weil wir  
                                  -- im Subselect auf die gleiche Tabelle  
                                  -- zugreifen, um äußeren von innerem Zugriff  
                                  -- unterscheiden zu können  
WHERE                             -- wobei uns nur die Datensätze interessieren,  
    s1.linux_timestamp = (        -- deren Timestamp mit  
    SELECT                        -- dem  
        MAX(s2.linux_timestamp)   -- maximalen Timestamp  
    FROM                          -- aus der Tabellle  
        stream s2                 -- stream, hier zur Unterscheidung über  
                                  -- den Alias s2 angesprochen,  
                                  -- übereinstimmt  
    WHERE                         -- wobei nur die Datensätze interessieren,  
        s1.user_id = s2.user_id   -- bei denen die user_id innen und außen  
                                  -- gleich ist.  
                                  -- Das ist die Korrelation des Subselects  
                                  -- mit der äußeren Abfrage.  
)  
ORDER BY                          -- Ergebnisse bitte  
    s1.linux_timestamp DESC       -- nach Timestamp-Spalte absteigend sortiert  

Ergebnis:

stream_id   user_id     type_id     linux_timestamp
----------- ----------- ----------- ---------------
5           320         1           1268615567
4           230         3           1268615555
2           180         2           1268615483

Im zweiten Schritt besorgen wir uns über entsprechende Joins die Daten aus den anderen Tabellen und limitieren die Ergebnismenge auf zwei Datensätze:

SELECT  
    s1.stream_id,  
    s1.user_id,  
    s1.type_id,  
    s1.linux_timestamp,  
    b.nickname,  
    b.vorname,  
    b.vornameview,  
    t.type_text,  
    t.icon  
FROM  
    stream s1  
INNER JOIN                           -- ganz normale INNER JOINs  
    benutzer b  
ON  
    s1.user_id = b.user_id  
INNER JOIN  
    `type` t                         --  
ON  
    s1.type_id = t.type_id  
WHERE  
    s1.linux_timestamp = (  
    SELECT  
        MAX(s2.linux_timestamp)  
    FROM  
        stream s2  
    WHERE  
        s1.user_id = s2.user_id  
)  
ORDER BY  
    s1.linux_timestamp DESC  
LIMIT 2                              -- limitiere die Ergebnismenge

Ergebnis (wie gewünscht):

stream_id   user_id     type_id     linux_timestamp nickname   vorname    vornameview type_text  icon
----------- ----------- ----------- --------------- ---------- ---------- ----------- ---------- ----------
5           320         1           1268615567      jonas      Jonas      1           Text 1     Icon 1
4           230         3           1268615555      theo       Theodor    1           Text 3     Icon 3

Freundliche Grüße

Vinzenz

[Martin Jakobi]

Hallo Vinzenz,

vielen Dank für deine äußerst hilfreiche und gut erklärte Antwort. Dank dir hab ich heute wichtige Dinge dazugelernt. Ich werd wohl eine Menge umschreiben, die Joins welche ich bisher mache erfolgen stets wie in meinem ersten Beitrag. Oder ist das nicht weiter schlimm?

Jedenfalls werde ich deine Erklärung dann mal entsprechend Umsetzen :-)

Liebe Grüße
Benedikt

[Ilja]

moin,

Zu meinem Problem:
Ich nutze MySQL - 5.0.32

lach, so kann man es auch sehen, aber dazu weiter unten mehr....

Nun möchte ich mit einer Abfrage wie der gleich folgenden erreichen, dass mir die (in diesem Beispiel) zwei neuesten Einträge aus der Tabelle stream angezeigt werden.

viele sind sich der problematik von der aussage "die zwei neusten einträge" nicht bewußt. was ist den, wenn die zwei neusten einträge nicht eindeutig sind, sondern es mehr gibt, wie zum beispiel bei olympia, die zweiteschnellste zeit von zwei athleten erreicht wurde. man muss sich gedanken darüber machen, wie man darauf reagiert.

Dabei soll nur ein Eintrag pro User angezeigt werden. In meinem Beispiel in der Tabelle stream sollen also nur Eintrag 5 und 4 ausgegeben werden (also die neuesten des jeweiligen Users).

wenn es pro user wäre, wären das aber drei einträge, nämlich User 180, 230 und user 320. willst du nun nur zwei datensätze, wo die user verschieden sein sollten oder aber pro user einen eintrag ?

Möglicherweise verstehe ich die Funktion GROUP BY nicht richtig. Habt ihr eine Lösung für mich?

jetzt kommen wir zurück zu dem teil, warum ich mir ein lachen nicht verkneifen konnte. und ich befürchte dein beitrag bekommt ein wenig zuviel meines frust auf MySql ab. aber das liegt einfach an der summe der beiträge, die ebenfalls dein problem haben. ich hoffe ja immer noch Vinzenz macht mal eineen weiteren beitrag hier bei selfhtml dies bezüglich, damit man darauf nur noch verlinken kann. sonst werde ich noch zu einem MySql "hasser".

wie auch immer, jedes andere dbms würde dir auf deine abfrage eine fehlermeldung ausgeben und das aus guten grund. normalerweise darf man nur spalten innerhalb einer gruppierung ausgeben, über die man auch gruppiert hat oder aber die eine aggregat-funktion benutzen. mysql geht einen anderen weg und zwar ausschließlich aus performance-gründen und erzeugt da mit wesentlich mehr probleme als nutzen. du bekommst zufällige werte dabei raus und die meisten merken es noch nicht mal, weil es eben keine fehlermeldung gibt.

aber zurück zum problem, willst du wirklich nur zwei datensätze oder pr user einen und was willst du bei zeitgleichheit machen, abschneiden oder beide anzeigen ?

Ilja