SQL row_number() - how to ignore nulls?

Question

I need to add counters to users activity, using this query:

select PERSON_ID, TIMESTAMP, 
  row_number() over (partition by  PERSON_ID order by  TIMESTAMP asc) as PERSON_COUNTER
from table1;

This works well, but it counts also the case where PERSON_ID is null and assigns counters as if null was a name of a user. What I want is the counter to be null in these cases - any ideas?

Answer 1

Yes you can use a query like below

select 
PERSON_ID, TIMESTAMP, CASE WHEN PERSON_ID IS NULL THEN NULL ELSE PERSON_COUNTER END AS PERSON_COUNTER
from
(
select PERSON_ID, TIMESTAMP, 
  row_number() over (partition by  PERSON_ID order by  TIMESTAMP asc) as PERSON_COUNTER
from table1
) t;