SQL Richtig sortieren mit GROUP BY?

[Schumiel]

Hallo,

ich habe folgendes SQL-Query:

SELECT count(*) AS anzahl, user_id, datum FROM activity WHERE status = 1 GROUP BY user_id ORDER BY anzahl DESC, datum DESC LIMIT 5

Der Datenbestand ist so, dass jede "user_id" mehrere Datensätze in der Tabelle "activity" hat. Das bedeutet auch, dass jeder Datensatz ein andere Datum (timestamp) hat. Nun möchte ich in diesem SQL-Query, dass bei der Ausgabe des Querys, dass Datum drin ist, was am Kürzesten zurück liegt. Und hatte folgendes versucht:

SELECT count(*) AS anzahl, user_id, MAX(datum) FROM activity WHERE status = 1 GROUP BY user_id ORDER BY anzahl DESC, datum DESC LIMIT 5

Ich meine es so auch schon mal gemacht zu haben. Hat aber nicht den gewünschten Erfolg gebracht. Jemand eine Idee?

Ich nutze MYSQL 5.7

 

[floq0r]

Ich vermute mal das datum im sort part wird ein problem verursachen

 

[LordArgaron]

ORDER BY anzahl DESC, MAX(datum) DESC LIMIT 5
sonst wird ja mit datum etwas anderes sortiert als das select vorgibt

 

[jb_alvarado]

@floq0r meinst du nicht anzahl? Das kommt ja vor datum. Datum sollte doch nur geordnet werden, wenn anzahl doppelte Einträge (Absatz: Order By Multiple Columns) enthält.

 

[floq0r]

Ich bin von TSQL ausgegangen, dort kann ich aggregierte results nur nach Feldern sortieren die auch im SELECT statement vorkommen. Nachdem datum nicht mehr im SELECT statement vorkommt könnte das zum Problem werden. Vielleicht geht MySQL aber auch anders damit um.

 

[h00bi]

Nun möchte ich in diesem SQL-Query, dass bei der Ausgabe des Querys, dass Datum drin ist, was am Kürzesten zurück liegt

Unterabfrage mit TOP1 und datum desc

 

[SomeDifferent]

Dein Problem ist, dass du in deinem ORDER BY nur Spalten verwenden kannst, die du auch gruppiert hast.


Mich verwirrt dein LIMIT 5 etwas? Willst du die letzten 5 Activities pro Benutzer haben? Oder pro Benutzer eine Activity, aber maximal 5 Datensätze?


Ich würde eine Unterabfrage bauen, die die Anzahl an Datensätze mit größerem Datum pro Benutzer ermittelt. Dann kannst du einfach Filtern :

SELECT
    a.user_id,
    u.name AS user_name,
    a.datum,
    a.status
FROM
    Activity a
JOIN
    Users u ON a.user_id = u.user_id
WHERE
    (
        SELECT
            COUNT(*)
        FROM
            Activity a2
        WHERE
            a2.user_id = a.user_id
            AND a2.datum >= a.datum
    ) <= 5
ORDER BY
    a.user_id,
    a.datum DESC;

https://www.db-fiddle.com/f/5bZ1EfsFtHqr61kyreQqRn/0


Falls du pro Benutzer nur den letzten Datensatz benötigst, ginge auch folgendes :

SELECT
    user_id,
    MAX(datum) AS letztes_datum
FROM
    Activity
WHERE
    status = 1
GROUP BY
    user_id

 

[Schumiel]

Blöder Fehler. Habe die Lösung heute gefunden.

SELECT count(*) AS anzahl, user_id, MAX(datum) AS datums FROM activity WHERE status = 1 GROUP BY user_id ORDER BY anzahl DESC, datums DESC LIMIT 5

Ich danke euch trotzdem.