BigQuery SQL Rank() Over() with an exception

Question

I'm trying to create a rank() over() but need it to return null when meeting a certain criteria. Then continue the rank on the next row.

Example of what i'm trying to accomplish is the column rank_over_except in this image.

It is ranking() over() the identifier and is ordered by original_nr column. In this case it doesn't "rank" when the fruit is a pear.

I have tried using a CASE WHEN THEN statement. But that is simply continuing the count. The null would simply replace a 2 on the 2nd row of rank_over_except in this example. And the third row would be a 3. So that's not working as expected.

I don't see any option to write the rank() over(). Maybe there's another way of doing this and not use rank()? I've gone through the BigQuery docs, but no luck in finding a solution.

Answer 1

The solution that worked fine for my case is based on the comment of @SR3142. Maybe with row_number @GMB's solution can also work, but the size of the data was neglegible in my case.

select 
  t.*, 
  ROW_NUMBER() OVER (PARTITION BY identifier ORDER BY original_nr ASC) rank_over_except 
from table as t 
  WHERE t.fruit NOT IN ("pear", "apple")
UNION ALL
SELECT
  NULL AS rank_over_except, 
  t.* 
FROM table AS t 
WHERE 
  t.fruit IN ("pear", "apple")

Reason to use row_number() over rank() is that row_number does not take in to account the data from the input column. So it's an actual "dumb" next in line number.