PostgreSQL column to keep track of number of appearances of a certain kind

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.

score 0 · Answer 1 · answered Sep 01 '21 at 23:27

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.

PostgreSQL column to keep track of number of appearances of a certain kind

1 Answers1