MySQL grouping and getting newest rows

Question

I would like to get newest row in column , But i need to get them just before grouping them each other. If i put Order by after group by, mysql ordering them which they before grouped.

What i tried to do, grouping messages for user's last messages each other ( like a facebook chat . )

If client had a 3 conversations , he/she need to see message list as last dialog :

( Suppose that , the mobile user nickname is Rob, and he is listing his message history... )

Rob -> Thomas ( Hello, I will be back asap. )

Dyne -> Rob (Where are you ?)

Rob -> Danilla (How re you ?)

SELECT 
    u.userNickName, 
    u.userFBID, 
    m.didRead, 
    max(m.messageID) messageID, 
    m.messageContent, 
    m.srcUserID, 
    m.destUserID, 
    m.messageSendDate
FROM 
users u, messages m
WHERE
(m.srcUserID='122' || m.destUserID ='122')
AND
u.userID =  m.destUserID
GROUP BY
u.userNickName

score 0 · Answer 1 · answered Sep 10 '13 at 14:57

0

What you're looking for is not group but LIMIT - you can order the result in a desc order according to date and LIMIT the resultset to contain the last 3 messages:

SELECT 
    u.userNickName, 
    u.userFBID, 
    m.didRead, 
    max(m.messageID) messageID, 
    m.messageContent, 
    m.srcUserID, 
    m.destUserID, 
    m.messageSendDate
FROM 
users u, messages m
WHERE
(m.srcUserID='122' || m.destUserID ='122')
AND
u.userID =  m.destUserID
ORDER BY m.messageSendDate DESC
LIMIT 3

answered Sep 10 '13 at 14:57

Nir Alfasi

53,191
11
86
129

The result will be 3 messages no matter who sent to that message, I mean if first 2 message belongs to Rob-Thomas, user will not see the other messages . – Onder OZCAN Sep 10 '13 at 15:49
@InnaKamoze This answer refers to your question the way it was put, if you have additional restrictions - please consolidate it into your question with relevant examples. – Nir Alfasi Sep 10 '13 at 16:25
I just tried your sql, And it worked what i said to. It just fetct 3 rows without categorized by users... – Onder OZCAN Sep 11 '13 at 06:21
You never defined the "categorizing by users" requirement in your question, further, you don't explain how can it be done, by `m.destUserID` ? by `m.srcUserID` ? by another parameter ? If you want more help you'll have to provide more details, examples of a few rows in the table and what would you want to ideally have as an output of the query. Without such additional information it's impossible to guess what you want to achieve... – Nir Alfasi Sep 11 '13 at 06:33
I wrote : "What i tried to do, grouping messages for user's last messages each other ( like a facebook chat . )". Also : Rob -> Thomas ( Hello, I will be back asap. ) Dyne -> Rob (Where are you ?) Rob -> Danilla (How re you ?) Btw. Rob is me in that case. I wanna list every users messages in each row ordering by last messages . Ex: If we had conversation, and if i sent you last message, Sql should get our last conversation message and other conversations as same case ... – Onder OZCAN Sep 11 '13 at 07:05

Onder OZCAN · Accepted Answer · 2013-09-12T17:18:21.623

I solved my question with using subquery and group by with following SQL Statement :

SELECT 
    u.userNickName,
    u.userFBID,
    m.didRead,
    m.messageID,
    m.messageContent,
    m.srcUserID,
    m.destUserID,
    m.messageSendDate 
FROM 
    users u, 
    messages m 
WHERE 
    (u.userID = m.srcUserID or u.userID = m.destUserID) 
AND 
CASE 
    WHEN m.srcUserID='122' THEN u.userID= m.destUserID
    WHEN m.destUserID ='122' THEN u.userID= m.srcUserID
    ELSE -1
END 
AND 
m.messageID in (select max(m.messageID) from users u, messages m where (u.userID = m.srcUserID or u.userID = m.destUserID) group by u.userNickName);

MySQL grouping and getting newest rows

2 Answers2