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

isqlquery, mysql, sql

Solution

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
             );

Problem

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.

Original source