CODEWITHSUNDEEP
phpmysql
user2178521
user2178521

Reputation: 843

Mysql group by fetch last row

SELECT * FROM conversation_1 
LEFT JOIN conversation_2
ON conversation_1.c_id = conversation_2.c_id

LEFT JOIN user
ON conversation_2.user_id = user.user_id

LEFT JOIN message
ON conversation_1.c_id = message.c_id

WHERE conversation_1.user_id=1
GROUP BY message.c_id


conversation_1      conversation_2
c_id    user_id     c_id    user_id
1       1           1       2
2       1           2       3
3       2

I have a message DB build in Mysql

I make 4 tables user, conversation_1, conversation_2, message

when user try to open his message box, it will fetch out all conversations(conversation_1)

than join to user conversation_2 and use conversation_2 to find out which user

than join to the message.

c_id    user_id     user_name   message
1       2           Alex        Hi user_1, this is user_2
2       3           John        hi user_3, user_2 don't talk to me

it works fine, however I want to display the message from last row GROUP BY

currently it display the 1st row in this group.

ps.conversation_1.c_id is auto increment and the c_id will insert to conversation_2 who has join this conversation

Upvotes: 0

Views: 140

Answers (2)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

select * from (SELECT * FROM conversation_1 
LEFT JOIN conversation_2
ON conversation_1.c_id = conversation_2.c_id    
LEFT JOIN user
ON conversation_2.user_id = user.user_id    
LEFT JOIN message
ON conversation_1.c_id = message.c_id    
WHERE conversation_1.user_id=1
order by conversation_1.c_id desc) finalData
GROUP BY message.c_id

Upvotes: 1

eggyal
eggyal

Reputation: 125865

Beware that, as documented under MySQL Extensions to GROUP BY:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

This is what is happening to select the message (and potentially other columns) in your existing query.

Instead, you want the groupwise maximum:

SELECT messages.* FROM messages NATURAL JOIN (
  SELECT c_id, MAX(m_id) m_id FROM messages GROUP BY c_id
) t

Upvotes: 1

Related Questions