Row numbering based on contiguous data?-CodePudding

I need to assign numbers to rows based on a date. The rule is that the same number is assigned to multiple contiguous rows with the same date. When a row's date value differs from the previous row's date value, the number is incremented. The result set would look something like this (the first column would be used to determine row order):

1     7/1/2021     1
2     7/2/2021     2
3     7/2/2021     2
4     7/1/2021     3
5     7/2/2021     4

The value of the date is not what' relevant in this case. As you can see, there are repeats of the same date that get assigned different numeric values because they are not contiguous. I'm struggling to figure out how I would accomplish this.

CodePudding user response：

This is a Gaps & Islands problem. You need to provide the extra ordering columns for the query to make sense.

If you added these, the solution would go along the lines of:

select
  d,
  1   sum(inc) over(order by ordering_columns) as grp
from (
  select d, ordering_columns,
    case when d <> lag(d) over(order by ordering_columns) then 1 else 0 end as inc
  from t
) x
order by ordering_columns