hello experts;
I was just wondering is the use of nulls first in the row_number really necessary. I tested it out in the sample data below and it gives me the same result in both cases
case 1 using nulls first
with t as
(
select null as id, 1 as ver from dual
union all
select 1 as id, 1 as ver from dual
union all
select 1 as id, 2 as ver from dual
)
,p as (
select id
,row_number() over (partition by id order by ver desc nulls first) as rn
from t)
select * from p
where p.rn = 1
results
ID RN
1 1
null 1
case 2 without using nulls first
with t as
(
select null as id, 1 as ver from dual
union all
select 1 as id, 1 as ver from dual
union all
select 1 as id, 2 as ver from dual
)
,p as (
select id
,row_number() over (partition by id order by ver desc) as rn
from t)
select * from p
where p.rn = 1
ID RN
1 1
null 1