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 ?

Answer 1

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;

Answer 2

You are looking for dense_rank()

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