I have this query:
SELECT customer.cus_id,customer.fname,customer.lname,customer.image, message.message ,message.msg_id,message.from_cus,message.to_cus, message.sent_time FROM customer INNER JOIN message ON cus_id=message.from_cus OR cus_id=message.to_cus WHERE customer.cus_id IN ( 31,26) AND ( from_cus =25 OR to_cus = 25 ) AND customer.cus_id NOT IN(25) ORDER BY message.sent_time DESC GROUP BY customer.cus_id ;
where 31, 26 are the cus_id of the customers whose last messages I want to fetch.I am getting all the messages sorted asscording to time between the 1 and ( 31,26 ) customer if I remove GROUP BY constraint. But adding it gives me this syntax error
[Error code:1064 SQL state:42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY customer.cus_id' at line 11
I have disabled ONLY_FULL_GROUP_BY SQL mode. Still I dont know How to fetch the last message.
Advertisement
Answer
This is not an aggregation operations, this is a filtering operations.
Use window functions:
SELECT cm.* FROM ( SELECT customer.cus_id,customer.fname,customer.lname,customer.image, message.message ,message.msg_id,message.from_cus,message.to_cus, message.sent_time, ROW_NUMBER() OVER (PARTITION BY customer.cus_id ORDER BY message.sent_time DESC) as seqnum FROM customer INNER JOIN message ON cus_id=message.from_cus OR cus_id=message.to_cus WHERE customer.cus_id IN ( 31,26) AND ( from_cus =25 OR to_cus = 25 ) AND customer.cus_id NOT IN(25) ) cm WHERE seqnum = 1 ORDER BY msg_id DESC;