Fetching last duplicate row with the count of times that row was duplicated in PostgreSQL?-CodePudding

Here is my current view:

select u.*
from users u
join(
      select iid, usr, position,max(created_at) as ca
      from users
      group by iid, usr, position
    
    ) j on j.ca = u.created_at

This fetches the list of most recent duplicated rows.

EX TABLE: 10001 User 1 intern 2022-10-14 00:00:00
          10002 User 2 intern 2022-10-15 00:00:00
          10003 User 3 intern 2022-10-13 00:00:00
          10003 User 3 p.a    2022-10-15 00:00:00
          10001 User 1 p.a    2022-10-16 00:00:00

After using the query:

          10001 User 1 p.a    2022-10-16 00:00:00
          10003 User 3 p.a    2022-10-15 00:00:00
          10002 User 2 intern 2022-10-15 00:00:00

What I'm trying to do now is to add an extra column to also add a count of the duplicate rows.

Intended Results:

          10001 User 1 p.a    2022-10-16 00:00:00 [2]
          10003 User 3 p.a    2022-10-15 00:00:00 [2]
          10002 User 2 intern 2022-10-15 00:00:00 [1]

Is there a way to do this or do I have to write another query to get the count? I was also thinking of layering max with the count inside join but didn't work probably because I was using it wrong.

CodePudding user response：

If you need the number of rows involed in each group by juts add count(*)

select u.*, j.ca, j.num_dup
from users u
join(
      select iid, usr, position,max(created_at) as ca, count(*) as num_dup
      from users
      group by iid, usr, position
    
    ) j on j.ca = u.created_at