Similar to how ROW_NUMBER and RANK apply numbers to rows within a partition, I want to apply a sequential number to each partition, with each number within the partition being the same.

For example, given the following data;

CREATE TEMP TABLE test_data (
    id serial, 
    group_id int not null, 
    item_name varchar(20) not null
);

INSERT INTO test_data (
    group_id, 
    item_name
)
VALUES 
    (1, 'car'),
    (1, 'van'),
    (1, 'motorbike'),
    (3, 'helicopter'),
    (3, 'plane'),
    (6, 'boat'),
    (6, 'submarine');

SELECT group_id, item_name FROM test_data;

I want to partition these results by the group_id, and have each distinct group_id to be sequential, with all matching group_ids having the same value, i.e.

How can I achieve this?

>Solution :

demo:db<>fiddle

dense_rank() does this:

SELECT 
    group_id, 
    item_name,
    dense_rank() OVER (ORDER BY group_id) as group_running_no
FROM test_data;