mysql left join select top value from one table <p>I have the next query:</p> <pre><code>SELECT chat.*, message.time as last_message, message.content as last_message_content FROM chat LEFT JOIN message ON chat.id = message.chat_id GROUP BY chat.id ORDER BY message.time ASC </code></pre> <p>now, I want to pick the newest message.time, but right now it gives me the first one..</p> <p>any idea on how this could be accomplished?</p> <p>thanks</p>