Get the last entry on LEFT JOIN a table

Question

I have a query

SELECT cm.posted_at, s.id, s.name, s.device, co.status, co.last_seen, d.device_id 
FROM station s LEFT JOIN chat_online co 
ON s.id = co.station_id AND s.device = co.device_id 
LEFT JOIN device d ON s.device=d.id 
LEFT JOIN chat_message cm ON cm.station_id=s.id 
WHERE s.status='1' AND (co.role='station' OR co.role IS NULL) 
GROUP BY s.id 
ORDER BY cm.posted_at DESC

where in chat_message have multiple entry for one station while executing above query i am getting the first entry of chat_message corresponding to each station_id. How can i retrieve the last entry of chat_message while left joining the chat_message with station

Thank in Advance

Answer 1

As i understood , you have data like,

and you need to query like this,

select cm.posted_at, s.id, s.name, s.device from station s
JOIN      (
              SELECT    MAX(posted_at) posted_at,stationId
              FROM      chat_message 
              GROUP BY  stationId
          ) cm ON (cm.stationId = s.id)