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

Question

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;

klin · Accepted Answer · 2019-06-03 14:00:09Z

4

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.

edited Jun 3, 2019 at 14:00

answered Oct 11, 2017 at 23:08

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

corky Over a year ago

This provides the numbers, how do I update the table with these numbers?

igorkf Over a year ago

How could I increment counter when the values DON'T duplicate (i.e., the opposite case) ?

klin Over a year ago

@igorkf - please ask a new question with sample data and expected output.

zambonee · Accepted Answer · 2017-10-11 23:53:33Z

3

Check out ROW_NUMBER().

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

edited Oct 11, 2017 at 23:53

answered Oct 11, 2017 at 23:01

zambonee

1,64713 silver badges17 bronze badges

4 Comments

corky Over a year ago

That displays the numbers very nicely but it does not populate the new column.

corky Over a year ago

This returns the following error: ERROR: relation "t" does not exist LINE 1: UPDATE t set seq = seq2 FROM (

zambonee Over a year ago

Turns out my UPDATE statement does not work in PostGres (but it does work in SQL Server). Your table needs a primary key to update, or you can do something like stackoverflow.com/a/6578705/1933308

corky Over a year ago

thanks, I used ctid as per the link that klin provided

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related