CODEWITHSUNDEEP
postgresql
corky
corky

Reputation: 185

Postgresql - increment counter in rows where a column has duplicate value

I have added a column (seq) to a table used for scheduling so the front end can manage the order in which each item can be displayed. Is it possible to craft a SQL query to populate this column with an incremental counter based on the common duplicate values in the date column?

Before

------------------------------------
| name | date_time           | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 |     |
| ABC2 | 16-01-2017 11:30:00 |     |
| ABC1 | 16-01-2017 11:30:00 |     |
| ABC3 | 17-01-2017 10:00:00 |     |
| ABC3 | 18-01-2017 12:30:00 |     |
| ABC4 | 18-01-2017 12:30:00 |     |
| ABC1 | 18-01-2017 12:30:00 |     |
------------------------------------

After

------------------------------------
| name | date_time           | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 | 0   |
| ABC2 | 16-01-2017 11:30:00 | 0   |
| ABC1 | 16-01-2017 11:30:00 | 1   |
| ABC3 | 17-01-2017 10:00:00 | 0   |
| ABC3 | 18-01-2017 12:30:00 | 0   |
| ABC4 | 18-01-2017 12:30:00 | 1   |
| ABC1 | 18-01-2017 12:30:00 | 2   |
------------------------------------

Solved, thanks to both answers. To make it easier for anybody who finds this, the working code is:

UPDATE my_table f
    SET seq = seq2
    FROM (
        SELECT ctid, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY ctid) -1 AS seq2
        FROM my_table
        ) s
    WHERE f.ctid = s.ctid;

Upvotes: 3

Views: 9053

Answers (2)

klin
klin

Reputation: 121764

Use the window function row_number():

with my_table (name, date_time) as (
values

('ABC1', '15-01-2017 11:00:00'),
('ABC2', '16-01-2017 11:30:00'),
('ABC1', '16-01-2017 11:30:00'),
('ABC3', '17-01-2017 10:00:00'),
('ABC3', '18-01-2017 12:30:00'),
('ABC4', '18-01-2017 12:30:00'),
('ABC1', '18-01-2017 12:30:00')
)

select *, 
    row_number() over (partition by name order by date_time)- 1 as seq
from my_table
order by date_time;

 name |      date_time      | seq 
------+---------------------+-----
 ABC1 | 15-01-2017 11:00:00 |   0
 ABC1 | 16-01-2017 11:30:00 |   1
 ABC2 | 16-01-2017 11:30:00 |   0
 ABC3 | 17-01-2017 10:00:00 |   0
 ABC1 | 18-01-2017 12:30:00 |   2
 ABC3 | 18-01-2017 12:30:00 |   1
 ABC4 | 18-01-2017 12:30:00 |   0
(7 rows)

Read this answer for a similar question about updating existing records with a unique integer.

Upvotes: 4

zambonee
zambonee

Reputation: 1647

Check out ROW_NUMBER().

SELECT name, date_time, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY name) FROM [table]

Upvotes: 3

Related Questions