How to get group number in postgresql?

Question

I have a use case where I want to assign a unique increasing partition number to each partition when using a window query in postgres.

For example, I want to get an output like this:

Partition Key | Row_Num() | Partition Number  
Apple         | 1         | 1
Apple         | 2         | 1
Oranges       | 1         | 2
Oranges       | 2         | 2
Pear          | 1         | 3
Pear          | 2         | 3

Basically, apart from row_num() which gives us unique value within each "window", I want to have a number which is unique for each "window" How do I achieve this ? Is there any built in function in postgres for this ?

score 11 · Accepted Answer · answered Jun 04 '18 at 12:26

11

You seem to want:

select partitionkey,
       row_number() over (partition by partitionkey order by partitionkey) as row_num,
       dense_rank() over (order by partitionkey) as partition_number
from t;

answered Jun 04 '18 at 12:26

Gordon Linoff

1,242,037
58
646
786

Thanks a lot! This works for me. Been trying to solve this problem since a long time. – Pratik Singhal Jun 04 '18 at 12:36

Yogesh Sharma · Answer 2 · 2018-06-04T12:28:45.903

6

You are looking for dense_rank()

. . .
dense_rank() over (order by PartitionKey) as number

edited Jun 04 '18 at 12:28

answered Jun 04 '18 at 12:26

Yogesh Sharma

49,870
5
26
52

Dense rank doesn't work in this case. It won't give a unique value for each partition. – Pratik Singhal Jun 04 '18 at 12:27
Yes, It is working. Thanks! Accepting the other answer, as it was a few seconds faster, and I can't think of any other criteria. – Pratik Singhal Jun 04 '18 at 12:35

How to get group number in postgresql?

2 Answers2