SQL Question

Group by table name desc not working

I'm trying to work on simple messaging system. It is a page which contains the last message sent by each user. This is my table

-message

id person1 person2 message mess_date
1 2 q hii 2016-07-11 00:00:00.000000
2 q 2 hello 2016-07-10 00:00:00.000000
3 2 q how r u 2016-07-12 00:00:00.000000
4 2 1 message 2016-07-10 00:00:00.000000
5 q 2 nooooo 2016-07-13 00:00:00.000000


-login

id roll
1 q
2 1
3 2


Now I'm trying to fetch messages using this query --

suppose $roll="2";
select login.roll as userid, m1.id, m1.message, m1.mess_date from message as m1,login where ((m1.person1=? and login.roll=m1.person2) or (m1.person2=? and login.roll=m1.person1)) group by login.roll order by m1.mess_date desc');
$stmt->bind_param('ss', $roll, $roll);


the output has 2 results :

q - hiii - 2016-07-11 00:00:00.000000
1 - message - 2016-07-10 00:00:00.000000


But I want it to be ordered by mess_date i.e.,

q - nooooo - 2016-07-13 00:00:00.000000
1 - message - 2016-07-10 00:00:00.000000

Answer

Your SQL group is not used properly, try the following:

Assume id always growing by time, otherwise you need to do via max(mess_date):

SELECT msg1.*
FROM (
    SELECT login.roll AS userid, m1.id, m1.message, m1.mess_date 
    FROM message AS m1,login 
    WHERE (
        (m1.person1=? AND login.roll=m1.person2) OR
        (m1.person2=? AND login.roll=m1.person1)
    )
) AS msg1, (
    SELECT msg.userid, max(msg.id) AS maxid
    FROM (
        SELECT login.roll AS userid, m1.id, m1.message, m1.mess_date 
        FROM message AS m1,login 
        WHERE (
            (m1.person1=? AND login.roll=m1.person2) OR
            (m1.person2=? AND login.roll=m1.person1)
        )
    ) AS msg
    GROUP BY userid
) AS msg2
WHERE msg1.userid = msg2.userid
AND msg1.id = msg2.maxid
ORDER BY msg1.mess_date DESC

$stmt->bind_param('ssss', $roll, $roll, $roll, $roll);