Query to get only one row from multiple rows having same values

Question

I have a notification table as follows

|id|user_receiver|user_sender|post_id|action|date|is_read

here user_sender is the person who generated notification, user_receiver is one who got the notification, post_id is id of post, action can be like, comment etc, is_read is 1 if its read by receiver else 0

I want to get all notifications for loggedin user

query that I am using is

SELECT id, user_receiver, user_sender, post_id, action, 
 max(date) as date, is_read 
FROM notification 
WHERE user_receiver=$ses_user 
group by user_sender, action,post_id,is_read 
order by date desc

but it doesn't give me latest rows even though I am using max(date) and I also want to get number of notifications that are unread.

I want only one row if there are multiple rows having same post_id, user_sender and action together and that should be the latest one. For example a user likes a post, a row is added to the table, then user dislikes and likes again, then a new row is added again, I want only the new row only.

Answer 1

To get the latest row in MySQL, you need to use a join or correlated subquery:

SELECT id, user_receiver, user_sender, post_id, action, date, is_read
FROM notification n
WHERE user_receiver=$ses_user and
      date = (select max(date)
              from notification n2
              where n2.user_sender = n.user_sender and
                    n2.action = n.action and
                    n2.post_id = n.post_id and
                    n2.is_read = n.is_read
             )
order by date desc;

In other databases, you would simply use the row_number() function (or distinct on in Postgres).

EDIT:

For the biggest id:

SELECT id, user_receiver, user_sender, post_id, action, date, is_read
FROM notification n
WHERE user_receiver=$ses_user and
      id   = (select max(id)
              from notification n2
              where n2.user_sender = n.user_sender and
                    n2.action = n.action and
                    n2.post_id = n.post_id
             )
order by date desc;

If you want the number of rows where isread = 1, then you can do something like:

SELECT sum(is_read = 1)
FROM notification n
WHERE user_receiver=$ses_user and
      id   = (select max(id)
              from notification n2
              where n2.user_sender = n.user_sender and
                    n2.action = n.action and
                    n2.post_id = n.post_id
             );