Create serial based on 2 columns PostgreSQL

Question

I have a table with 2 columns, one has a gid (serial) with intended duplicate values, I need them like that, and another column with a number, I need a 3rd column ("serial" in blue in the image) that looks like that. A serial number for each different "gid" but with the series from the "count" column

I tried row_number () over (partition by count order by gid) but I dont' get the correct results

EDIT: As Gordon Linoff says, I tried that and i get this results, not working as expected...

EDIT2: It works, I just needed to order the row_number, but this alters the rest columns of my table, would be any way to make row_number work in order since beggining wthout needing to order later?

EDIT3: Actually, it works perfectly, the display was wrong

Gordon Linoff · Accepted Answer · 2020-05-07 14:35:08Z

1

I think you want both gid and count in the partition by:

select row_number () over (partition by gid, count order by gid)

You might have something more meaningful for the order by clause, if you have additional columns.

answered May 7, 2020 at 14:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Create serial based on 2 columns 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