PostgreSQL column to keep track of number of appearances of a certain kind [duplicate]

Question

I have to make a new column inside of an already existing database that keeps track of the number of occurences of a certain kind. Here's an example to be more clear:

id | eqid | etcd | this would be my new column
-------------------------
1  | 4   | abc  | 1
2  | 3   | def  | 1
3  | 1   | ghi  | 1
4  | 4   | jkl  | 2
5  | 3   | mno  | 2
6  | 4   | pqr  | 3

I am trying to make this column to be able to get all the EQID's of a certain value and get their position. For example: in the example above, let's say you want to get the first EQID that is equal to 4 and that has it's ETCD equal to "abc" in this example to become the second EQID equal to 4 thanks to a button and the second EQID to become the first, I would increment the one with the ETCD equal to "abc" and decrement the one with the ETCD equal to "jkl". I have tried finding answers everywhere but I haven't found an answer that could help me out. Thanks for any help. By the way this column must be saved it can not be a temporary column or anything like that.

Belayer · Accepted Answer · 2021-09-01 23:27:47Z

0

Use the Window function Row_Number(). See demo here

select id, eqid ,etcd 
     ,  row_number() over( partition by  eqid order by id) "This is new column"      
   from sample_data
   order by id;

You should look into and become familiar with Window Functions and the documentation references there.

answered Sep 1, 2021 at 23:27

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL column to keep track of number of appearances of a certain kind [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related