By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table mytable (
id int,
COL1 varchar(20),
COL2 varchar(20),
COL3 varchar(20)
);
insert into mytable values
(1, 'A' , 'G' , 'V2' ),
(2, 'A' , 'B' , 'V1' ),
(3, 'A' , 'C' , 'V1' ),
(4, 'A' , 'D' , 'V1' ),
(5, 'A' , 'D' , 'V2' ),
(6, 'A' , 'E' , 'V1' );
CREATE TABLE
INSERT 0 6
select *, case when col3 = 'V1'
then row_number() over (partition by COL1 order by id) -
row_number() over (partition by COL1, COL3 order by id)
else null
end as rnk
from mytable
order by id
| id | col1 | col2 | col3 | rnk |
|---|---|---|---|---|
| 1 | A | G | V2 | null |
| 2 | A | B | V1 | 1 |
| 3 | A | C | V1 | 1 |
| 4 | A | D | V1 | 1 |
| 5 | A | D | V2 | null |
| 6 | A | E | V1 | 2 |
SELECT 6