Reputation: 127
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.
Upvotes: 1
Views: 2800
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