Dense rank for duplicated values

Question

id     val       dense_rank
1       11   =>     1
2       11          1
3       22          2
4       33          3
5       33          3
6       11          4

What sql should like for getting this kind of dense_rank?

Answer 1

Use lag() to figure out what the previous value is, then a cumulative sum:

select t.*,
       count(*) filter (where prev_val is null or prev_val <> val) over (order by id) as dense_rank
from (select t.*, lag(val) over (order by id) as prev_val
      from t
     ) t;

Here is a db<>fiddle.