0

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

2
  • Please include sample output, and information on any other tables we would need to include to generate that.
    – RDFozz
    May 10, 2017 at 23:02
  • Ah - I assumed we needed to retrieve the name tied to rel_user_id (to get "Jack" for "Jack and 3 more users liked your post").
    – RDFozz
    May 11, 2017 at 14:19

1 Answer 1

0

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.