letzte Datensätze aller User vollständig abfragen (GROUP BY)

[klanor]

Hallo zusammen,
ich bin neu hier (habe mich soeben registriert) und komme direkt mit einer Frage um die Ecke, die ich alleine nicht lösen kann:

Ich möchte aus einer Liste von Dateien, die verschiedene User hochgeladen haben eine Ausgabe erhalten, in der von jedem User dessen neueste Datei enthalten ist.
Es gibt neben der UserId noch das Datum des Uploads, den Dateinamen (und eine Kategorie).

Mit folgendem Statement habe ich begonnen:

SELECT
  UserId,
  max( UpladDate )
FROM
  UserFiles
GROUP BY
  UserId

Die Ausgabe ist wie erwartet korrekt, aber ich möchte noch weitere Spalten bekommen, die ich nicht mit gruppieren will, weil dadurch das Ergebnis nicht mehr nur eine Datei pro User enthalten würde.
Nicht aggregierte Spalten in der Abfrage müssen aber auch mit gruppiert werden. Also scheint dieser Ansatz wohl nicht der richtige zu sein ...

Als Beispiel habe ich mal folgende Daten zur Auswahl:

UserId UploadDate FileName
1      2024-01-01 Datei1
1      2024-02-01 Datei2
2      2024-01-15 Datei3
2      2024-02-15 Datei4

Mit meiner Abfrage oben erhalte ich korrekt:

1      2024-02-01
2      2024-02-15

Ziel ist es aber, folgendes Ergebnis zu erhalten:

1      2024-02-01 Datei2
2      2024-02-15 Datei4

Hat jemand eine Idee, wie ich das lösen kann?

 

[akretschmer]

als einer von vielen Wegen:

postgres=# select * from klanor ;
 userid | uploaddate | filename 
--------+------------+----------
      1 | 2024-01-01 | Datei1
      1 | 2024-02-01 | Datei2
      2 | 2024-01-15 | Datei3
      2 | 2024-02-15 | Datei4
(4 rows)

postgres=# with foo as (select *, row_number() over (partition by userid order by uploaddate desc) from klanor) select userid, uploaddate, filename from foo where row_number = 1;
 userid | uploaddate | filename 
--------+------------+----------
      1 | 2024-02-01 | Datei2
      2 | 2024-02-15 | Datei4
(2 rows)

postgres=#

 

[klanor]

with foo as (select *, row_number() over (partition by userid order by uploaddate desc) from klanor) select userid, uploaddate, filename from foo where row_number = 1;

Danke. Das sieht schonmal gut aus, und ich kann es sogar nachvollziehen.

MySQL mag das aber so anscheinend nicht und bemängelt "row_number = 1" mit einem Syntax error.
Hast du vielleicht auch eine funktionierende Variante für MySQL?

 

[dabadepdu]

Hat jemand eine Idee, wie ich das lösen kann?

Das ist ein typisches Problem einer Group By Abfrage. Man will meistens etwas mehr, als sie im ersten Anlauf liefert.
Aber da Du die Hälfte schon hast, also eine korrekt ablaufende Group By Abfrage mit dem gewünschten Ergebnis, schaffst Du auch den 2. Teil.

Du musst Deine Group By Abfrage verwenden und sie als Filter für eine erneute Abfrage Deiner Rohdaten nutzen. Einfach die Abfrage noch mal gegen die Rohdaten joinen und dann von den gejointen / gefilterten /richtigen Datensätzen alle Spalten ausgeben, die Du benötigst.

Alternativ kannst Du eine Funktion suchen, die Dir in mySQL eine Analogie zu row_number () liefert und das Statement von @akretschmer nachahmen.

 

[klanor]

Danke. Letzteres hatte ich schon versucht, bin allerdings an meinem noch recht mangelhaften Db-verständnis gescheitert.

Wie kann ich denn die aktuelle Abfrage gegen die Rohdaten joinen? Ich habe darin ja nichts, was die gefundenen Datensätze eindeutig identifiziert.
Meine dilettantischen Versuche, das zu erreichen sind entweder fehlgeschlagen oder waren schon beim Formulieren eindeutig verkehrt.

Hier das, bei dem ich letztendlich gelandet bin. Natürlich mit nicht korrektem Ergebnis, aber ich stoße da an meine (engen) Grenzen des SQL Verständnisses.

SELECT * FROM UserFiles;

UserId - UploadDate - Filename
     1 - 2024-01-01 - Datei1 (not latest)
     1 - 2024-02-01 - Datei2 (latest)
     2 - 2024-02-15 - Datei4 (latest)
     2 - 2024-01-15 - Datei3 (not latest)

SELECT
  f1.UserId,
  max( f1.UploadDate ),
  f2.FileName
FROM
  UserFiles f1

  LEFT JOIN
      UserFiles f2
  ON
      f2.UserId = f1.UserId
    AND
      f2.UploadDate = f1.UploadDate

GROUP BY
    f1.UserId;

UserId - UploadDate - Filename
     1 - 2024-02-01 - Datei1 (not latest)
     2 - 2024-02-15 - Datei4 (latest)

 

[dabadepdu]

Ich habe darin ja nichts, was die gefundenen Datensätze eindeutig identifiziert.

Na klar hast Du das. Das Datumsaggregat (Max) und die UserID zeigen genau das, was Du haben willst. Das ist doch erstmal der Sinn und das tatsächliche Ergebnis, was Du bereits erreicht hast.
Du hast auch in #5 das Richtige versucht, aber dabei leider dein erstes Ergebnis zerstört. Dabei bist Du leider wie wahrscheinlich jeder Anfänger, der mySQL einsetzt, darüber gestolpert, dass Du eine falsche Abfrage geschickt hast, die aber keinen Fehler ausgibt, heimtückisch bei mySQL. Das ist schwierig zu verstehen, wenn man denkt, dass man alles richtig macht. Jede andere DB würde einen Fehler werfen bei Deiner letzten Abfrage.
Aggregat Abfragen (max, min, sum, avg, ..) müssen die Ausgabespalten entweder gruppieren oder aggregieren. Hattest Du oben eingehalten, unten nicht.
Also, Du nimmst Dir einfach Deine erste, richtige, funktionierende Abfrage setzt sie in Klammern, spendierst einen Alias und kannst sie so verwenden, als sei sie eine Tabelle. Dagegen machst Du den Join auf die Rohdaten, wie in Deiner zweiten Abfrage. Die Gruppierung bleibt dabei schön brav, unangetastet in der ersten, geklammerten Abfrage. Ich schreib es mal so symbolisch hin, weil ich zu faul bin und Du es auch so schaffst:
select <alles was ich brauche> from (Group By Abfrage mit jüngster pro user id) x
join rohdaten y on <joinkriterien>

Das könnte man dann noch als CTE machen, ändert aber nichts am Prinzip.

 

[klanor]

Sehr cool. Danke @dabadepdu .
Von alleine wäre ich da nicht drauf gekommen.

Jetzt klappt es mit

SELECT
  f1.UserId,
  f1.UploadDate,
  f1.FileName
FROM
  UserFiles f1
  INNER JOIN (
    SELECT
      UserId,
      MAX(UploadDate) AS MaxUploadDate
    FROM
      UserFiles
    GROUP BY
      UserId
  ) f2
  ON
    f1.UserId = f2.UserId
  AND 
    f1.UploadDate = f2.MaxUploadDate;

Und wenn man es dann sieht, fragt man sich, warum nicht geich so?!

Mein übergeordnetes Problem ist ja das, was wahrscheinlich sehr viele haben: Immer mal so mehr oder weniger erfolgreich mit SQL durchgewurschtelt ohne das System als solches richtig gelernt zu haben. An einer Aufgabe, die komplizierter ist, als das, womit man alltäglich zu tun hat, scheitert man dann.

Schön, dass ich wieder etwas lernen konnte. Danke dir und auch @akretschmer

 

[dabadepdu]

Schön, dass ich wieder etwas lernen konnte

Viele Wege führen nach Rom. Dieses Verfahren kannst Du Dir merken, für alle Aggregate Abfragen, bei denen Zusatzangaben zu einem Spitzenwert benötigt werden.
Und Du kannst gleich weiter machen, stell es auf CTE um. Ich mag die Syntax nicht, aber es lohnt sich bei Mehrfachverwendung bzw. aus Performancegründen.