Group and count user notifications i have a notifications table like this:  

  

i want to select all user 1 rows, but take only one row from rows with same parent id, and also have the count of those rows.  

so i can show to user: 'jack and 3 more users have liked your post'.  

many thanks
This should come close to meeting your need.

This assumes you want the rel_user_id from the most recently added row for a given user_id and parent_id, and that the row with the highest id should be the one most recently entered. If you want the rel_user_id with the highest value instead, .you can basically just use the agg subquery (changing MAX(ID) to MAX(rel_user_id).

Also, I didn't worrying about the join to the posts table - I'm guessing that parent_id is the foreign key column, and you've got that; you should be able to get there from here.

Here's the query:

SELECT ntf.user_id
      ,ntf.parent_id
      ,ntf.rel_user_id
      ,agg.notification_count
  FROM (SELECT user_id, parent_id, COUNT(*) as notification_count, MAX(id) as max_id
          FROM notifications
         WHERE user_id = 1
         GROUP BY user_id, parent_id
       ) agg
         INNER JOIN notifications ntf ON (    agg.user_id = ntf.user_id
                                          AND agg.parent_id = ntf.parent_id
                                          AND agg.max_id = ntf.id
                                         )
 ORDER BY user_id, parent_id
;


The agg subquery gets us one row for each user_id and parent_id; we'll put the WHERE clause specifying which user_id you want here, to limit how many records we're grouping. This gets us the count of rows for the user_id and parent_id combo, and gets use the "most recent" id.

We then tie back to notifications with an INNER JOIN to get the rel_user_id from the indicated id row.

Here's a db-fiddle.com link showing the query in action, with the sample data you provided.