How to group notifications in a feed

Personally, I would record the notification times exactly and then do something like this (I have not tested these specific solutions, but have done similar things before):

If you want notifications to be grouped by date :

SELECT about_resource_id,DATE(notification_time) AS notification_date,COUNT(*) FROM notification_tbl GROUP BY about_resource_id,DATE(notification_time);

      

        
        
        
      

    

By the clock (with division boundaries relative to the current time), you can do this:

SELECT 
    about_resource_id,
    TIMESTAMPDIFF(HOUR,NOW(),notification_time) AS hours_ago,
    COUNT(*) 
FROM notification_tbl 
GROUP BY about_resource_id,TIMESTAMPDIFF(HOUR,NOW(),notification_time);

      

        
        
        
      

    

For notifications grouped in other ways, I would write a suitable formula in the GROUP BY clause.

To indicate who last commented, I would do something like this:

SELECT 
    about_resource_id,
    DATE(notification_time) AS notification_date,
    SUBSTRING_INDEX(GROUP_CONCAT(counterparty_id,";"),";",1) AS last_commenter_id,
    COUNT(*) AS commenters 
FROM notification_tbl 
GROUP BY 
    about_resource_id,
    DATE(notification_time) 
ORDER BY notification_time DESC;

      

        
        
        
      

    

I used the MySQL-specific GROUP_CONCAT () function with the SUBSTRING_INDEX string function: Some alternative JOIN / aggregate methods for solving this problem can be difficult to refine because MySQL does not seem to support LIMIT in a subquery . You may need to consider the format of your commenter_id, commenter_name, or equivalent field; when adapting my solution.