Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Postgres window function to number partitions

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;
group_id item_name
1 car
1 van
1 motorbike
3 helicopter
3 plane
6 boat
6 submarine

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

group_id item_name group_running_no
1 car 1
1 van 1
1 motorbike 1
3 helicopter 2
3 plane 2
6 boat 3
6 submarine 3

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading