CODEWITHSUNDEEP
postgresql
Chloe
Chloe

Reputation: 127

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

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

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 table 3

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

Upvotes: 1

Views: 2800

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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;

Upvotes: 4

Related Questions