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

Answer 2

Hope this post will help, there is a sample query with illustration:

SELECT 
    BookId, 
    BookName, 
    BookCategory, 
    BookReferenceNumber, 
    COUNT( BookReferenceNumber ) AS HowManyRecs, 
    BookDetails, 
    BookStatus
FROM 
    tbl_BOOKS
WHERE (
    BookReferenceNumber LIKE '42324%'
    OR BookName LIKE '%42324%'
)    
AND (
    BookStatus = 'Available'
)
GROUP BY 
    BookReferenceNumber
HAVING (
    HowManyRecs > 0
)

(Taken from techqube.blogspot.in)