CODEWITHSUNDEEP
sqldatabasepostgresql
Marco
Marco

Reputation: 33

Ordered rank in PostgreSQL only for specific rows

I'm on PostgreSQL and I'm trying to find a query that can solve my problem.

This is what I need:

|COL1|COL2|COL3|RANK
| A  | G  | V2 | NULL
| A  | B  | V1 | 1
| A  | C  | V1 | 1
| A  | D  | V1 | 1
| A  | D  | V2 | NULL
| A  | E  | V1 | 2

In short, for consecutive rows, ordered by COL1-COL2, I need to get an ordered RANK for COL3 = V1.

I was thinking to use the LAG() function, for checking the next row, and then compare the value of COL3 between current row and next row, with something like this:

SELECT *, lag(COL3) over(partition by COL1 order by COL1, COL2) as COL_CHECK
FROM table

I don't really understand how can I manage this, because I need RANK column NULL when COL3 = V2, and increase RANK when COL3 = V1.

Thank you.

Upvotes: 0

Views: 49

Answers (1)

SelVazi
SelVazi

Reputation: 16043

If there exists a unique column ensuring deterministic ordering (such as a primary key), here's a solution employing the difference between two row numbers:

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

Demo here

Upvotes: 1

Related Questions