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.
| 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 :
dense_rank() does this:
SELECT
group_id,
item_name,
dense_rank() OVER (ORDER BY group_id) as group_running_no
FROM test_data;