How to assign row number using row_number() to a repeated values in postgresql

Question

I have some rows in data where agentid and customer ids are being repeated. like so

I want to assign a row number to each of the repeated value so that the result looks like this table below: Table 2:

agentid	customerid	row_number
26	1234	1
26	1234	2
26	1234	3
26	1234	4
26	1454	1
26	1256	1
26	1256	2
30	1256	1

I am new to PostgreSQL, so I tried this:

select agentid, customerid, row_number() OVER () from aa_dev.calls group by agentid, customerid having count(customerid) > 1;

This query above selected all the repeated pairs and assigned rows to each of them

but I want to create something that I mentioned above in table 2. Please guide me about this.

Tim Biegeleisen · Accepted Answer · 2021-01-13 17:01:05Z

4

You want:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY agentid, customerid ORDER BY random()) rn,
              COUNT(*) OVER (PARTITION BY agentid, customerid) cnt
    FROM aa_dev.calls
)

SELECT agentid, customerid, rn      
FROM cte
WHERE cnt > 1;

answered Jan 13, 2021 at 17:01

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to assign row number using row_number() to a repeated values in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related