MySQL GROUPBY show newest row-CodePudding

I have a messaging system I am working on that receives messages from my Facebook Business Page and stores the information in my database. The information provided in the callback is a Sender ID, Recipient ID, and message data. I am wanting to group all messages between the sender and recipient together and return only the newest result (by row ID number) at the top of the list.

For example :

ID | Sender ID  |   Recipient ID  | is_read
1  | 67890      |   12345         | 1
2  | 23232      |   12345         | 0
3  | 12345      |   67890         | 1
4  | 67890      |   12345         | 0
5  | 12345      |   23232         | 1
6  | 55555      |   12345         | 1

I don't want to show any results with Sender ID "12345".

The result I need should look something like this

Result | Row ID  |   Sender ID
1      | 4       |   67890
2      | 2       |   23232
3      | 6       |   55555

Here is my current query. Should return an Array with the newest message first no matter the senderid order. Currently, I get random results.

$sql = "SELECT id, senderid, sender_name, is_read FROM (SELECT id, senderid, sender_name, is_read FROM FB WHERE senderid != '".$fb_page_id."' GROUP BY senderid) AS f ORDER BY is_read ASC LIMIT ".$page_num * $perpage.", ".$perpage;

This has to be something simple.... just can't figure it out... lol.

CodePudding user response：

If you just need the sender and its latest id in the resultset, we can just use aggregation here:

select max(id) as last_id, sender_id
from fb
where sender_id != 12345
group by sender_id
order by last_id desc

If, on the other hand, you need the entire latest row per sender, you can use window functions:

select *
from (
    select fb.*, row_number() over(partition by sender_id order by id desc) rn
    from fb 
    where sender_id != 12345
) f
where rn = 1
order by id desc

You can add the limit clause after the order by if that's needed.

In MySQL < 8.0, where window functions are not supported, we can use a correlated subquery instead:

select *
from fb f
where sender_id != 12345 and id = (
    select max(f1.id) from fb f1 where f1.sender_id = f.sender_id
)
order by id desc