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

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

CodePudding user response：

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

CodePudding user response：

You do not need to specify NULL when setting default value in TIMESTAMP, it will return an error. Do it instead:

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 NULL,
  msg_type ENUM('text','link','file') default 'text',
  is_seen BINARY DEFAULT 0
  ) ENGINE=InnoDB;

    insert into messaging 
VALUES(  NULL,   123,  'ABC',  'THIS IS A TEXT',  DEFAULT,  DEFAULT,  NULL,  'text',  DEFAULT),
(  NULL,   456,  'DFG',  'TEXT THIS IS',  DEFAULT,  DEFAULT,  NULL,  'link',  DEFAULT),
(  NULL,   789,  'HIJ',  'MAY THE FORCE BE WITH',  DEFAULT,  DEFAULT,  NULL,  'link',  DEFAULT),
(  NULL,   456,  'KLM',  'YOU',  DEFAULT,  DEFAULT,  NULL,  'link',  DEFAULT);

I've found two ways to acheive to your need:

# UNION WAY
SELECT * FROM(
SELECT MAX(t.sender_id), 'sender_id' as type
FROM messaging t
union all
SELECT MAX(t2.receiver_id), 'receiver_id' as type
FROM messaging t2) t3;

# SUBSELECT WAY
SELECT MAX(sender_id) AS MAX_sender_id,
  (SELECT MAX(t2.receiver_id)
   FROM messaging t2) AS MAX_receiver_id 
FROM messaging t;

Check the result here -> SQL Fiddle

Let me know if it helped