CODEWITHSUNDEEP
mysqlsqljoingroup-bygreatest-n-per-group
Trombone0904
Trombone0904

Reputation: 4258

select data from database and get right order after grouping

I have the following sql query:

SELECT chat.*, user.vorname AS vorname, user.images AS userImage, nachrichten.ID as lastMessageID FROM `chat` 
LEFT JOIN `user` ON chat.userID = user.ID 
LEFT JOIN `nachrichten` ON chat.chatToken = nachrichten.chatToken
WHERE chat.userID != 1 AND chat.chatToken = chat.chatToken
ORDER BY nachrichten.ID DESC

The Output:

enter image description here

Now I would like to group the result by userID:

SELECT chat.*, user.vorname AS vorname, user.images AS userImage, nachrichten.ID as lastMessageID FROM `chat` 
LEFT JOIN `user` ON chat.userID = user.ID 
LEFT JOIN `nachrichten` ON chat.chatToken = nachrichten.chatToken
WHERE chat.userID != 1 AND chat.chatToken = chat.chatToken
GROUP BY chat.userID
ORDER BY nachrichten.ID DESC

The result:

enter image description here

But I get the lowest "lastMessageID" - I would like to get the highest ID

Dennis -> 97 (instead of 90)

Where is my fault?

Upvotes: 0

Views: 57

Answers (1)

GMB
GMB

Reputation: 222402

I understand that you want the id of the latest message per user. If so, I would recommend a correlated subquery rather than aggregation:

select 
    c.*, 
    u.vorname, 
    u.images as userimage, 
    (select max(n.id) from `nachrichten` n where n.chatToken = c.chatToken) as lastmessageid 
from `chat` c 
inner join `user` u on u.id = c.userid
where c.userid != 1

This query would work on any version of MySQL, including 5.7 or higher, where ONLY_FULL_GROUP_BY is enabled by default (which would cause your original query to fail, since the SELECT clause contains non-aggregated columns that do not belong to the GROUP BY clause).

Side notes:

  • table aliases make the query easier to read and write

  • I removed irrelevant condition chat.chatToken = chat.chatToken from the where clause; if your itent is to eliminate rows where chat.chatToken is null, just use c.chatToken is not null

Upvotes: 1

Related Questions