want to get each Groups latest row, group by multiple column

I need to get the latest row of each combinations of sender_id & receiver_id

my messaging table:

CREATE TABLE messaging (
  msg_id SERIAL,
  sender_id BIGINT  NOT NULL ,
  receiver_id varchar(255) NOT NULL ,
  msg text default NULL,
  media_link TEXT DEFAULT NULL,
  sent_time TIMESTAMP NOT NULL DEFAULT NOW(),
  received_time TIMESTAMP default NULL,
  msg_type ENUM('text','link','file') default 'text',
  is_seen BINARY DEFAULT 0
  ) ENGINE=InnoDB;

Sample data:

+------+---------+-----------+---------------+----------+-------------------+-------------+--------+-------+
|msg_id|sender_id|receiver_id|msg            |media_link|sent_time          |received_time|msg_type|is_seen|
+------+---------+-----------+---------------+----------+-------------------+-------------+--------+-------+
|1     |1        |10         |hi             |NULL      |2022-11-08 19:11:53|NULL         |text    |0      |
|2     |1        |10         |r u there?     |NULL      |2022-11-08 19:12:46|NULL         |text    |0      |
|3     |7        |10         |hi             |NULL      |2022-11-08 19:13:13|NULL         |text    |0      |
|4     |7        |10         |where r u from?|NULL      |2022-11-08 20:31:17|NULL         |text    |0      |
+------+---------+-----------+---------------+----------+-------------------+-------------+--------+-------+

ORDER BY latest with each sender_id receiver_id combination the result should look like this:

+------+---------+-----------+---------------+----------+-------------------+-------------+--------+-------+
|msg_id|sender_id|receiver_id|msg            |media_link|sent_time          |received_time|msg_type|is_seen|
+------+---------+-----------+---------------+----------+-------------------+-------------+--------+-------+
|2     |1        |10         |r u there?     |NULL      |2022-11-08 19:12:46|NULL         |text    |0      |
|4     |7        |10         |where r u from?|NULL      |2022-11-08 20:31:17|NULL         |text    |0      |
+------+---------+-----------+---------------+----------+-------------------+-------------+--------+-------+

I have tried this statement:

SELECT msg_id, 
       sender_id, 
       receiver_id, 
       msg, 
       media_link, 
       sent_time, 
       received_time, 
       msg_type, 
       is_seen  
FROM messaging  
WHERE sender_id = 10 OR receiver_id = 10 
GROUP BY sender_id,receiver_id  
ORDER BY msg_id DESC;

which gives there rows ascending order of each group:

+------+---------+-----------+---+----------+-------------------+-------------+--------+-------+
|msg_id|sender_id|receiver_id|msg|media_link|sent_time          |received_time|msg_type|is_seen|
+------+---------+-----------+---+----------+-------------------+-------------+--------+-------+
|3     |7        |10         |hi |NULL      |2022-11-08 19:13:13|NULL         |text    |0      |
|1     |1        |10         |hi |NULL      |2022-11-08 19:11:53|NULL         |text    |0      |
+------+---------+-----------+---+----------+-------------------+-------------+--------+-------+

But it is only showing oldest row of each group by combination.

I am still learning Mysql. Please help me

>Solution :

• if you need a recent row, then there must be a specific timeframe field that you are looking at i.e sent_time
• you can use that timeframe field in the ORDER BY by clause of the row_number() function to create a rank. This rank would give a value of 1 for the most recent timeframe field with the combination of sennder and receiver id (see partition by caluse )
• Lastly you can filter on rank_ = 1

with main as (
select 
*,
row_number() over(partition by sender_id, receiver_id order by sent_time desc) as rank_
from messaging
)
select * from main where rank_ = 1