MySQL query to fetch last record from each group involving 2 columns
<p>I'm writing a MySQL (MariaDB) query fetch the list of senders from which a user has received message. Like in Facebook inbox, I want recent senders to appear first which I've already already via following query:</p>

<pre><code>SELECT  DISTINCT user_id_s
    FROM  messages
    WHERE  id IN (
        SELECT  MAX(id)
            FROM  messages
            WHERE  user_id_r=390
            GROUP BY  user_id_s
                 )
    order by  id DESC 
</code></pre>

<p>It seems to work fine (not sure if it will work fine with large records like a million rows?). But the real problem is the it only fetches in order if the last message was received by a user and not the last replied.</p>

<p>Example:</p>

<p>User 390 has received messages from following users in this order:</p>

<ol>
<li>385</li>
<li>421</li>
<li>422</li>
<li>423</li>
<li>424</li>
<li>429</li>
<li>430</li>
<li>431</li>
</ol>

<p>So now user 431 is most recent and appears at top of the list, but if user 390 replied to user 431, it stays at bottom while I want this to appear at top based on the last interaction.</p>

<p>I hope I explained it well.</p>

<p><strong>EDIT:</strong> Here is an SQL fiddle: <a href="http://sqlfiddle.com/#!9/82417/2" rel="nofollow">http://sqlfiddle.com/#!9/82417/2</a></p>

<p>In this example, row id with '573' should appear at top replacing row# 572 and rest of the result must remain same. Also the second column which has user_id_s should give '431' at first row, just as it is now.</p>