row number in sql -keep it same for change in value

Question

I have a SQL questions: I want to populate rank for each record as a next up number. However I want to keep the rank same for records for which the adjust values are not same.

Sample data :

# | value | date1      | * rank (expected)
--+-------+------------+-------
1 | A     | 05/01/2020 |    1
1 | A     | 05/02/2020 |    2
1 | A     | 05/03/2020 |    3
1 | B     | 05/04/2020 |    3
1 | A     | 05/05/2020 |    3
1 | A     | 05/06/2020 |    4
1 | A     | 05/07/2020 |    5
1 | A     | 05/08/2020 |    6
1 | B     | 05/09/2020 |    6
1 | A     | 05/10/2020 |    6
1 | A     | 05/11/2020 |    7

I am looking for best way to populate the 4th column based on the first three.

Please describe the logic you want, in words. It is not obvious from your data. — GMB, Dec 21 '20 at 20:15
In addition to explaining the logic, please provide a database tag. — Gordon Linoff, Dec 21 '20 at 20:28

score 1 · Accepted Answer · answered Dec 21 '20 at 20:34

You don't provide a good explanation for the logic. But you seem to want to not count "B"s and the "A" on the next row.

This can be phrased as the following equation:

The row number minus
twice the number of Bs up to that row (to get the following row as well)
plus 1 if the row is B (because we just over subtracted the next row).

This can be implemented as:

select t.*,
       (row_number() over (partition by num order by date1) -
        2 * count(*) filter (where value = 'B') over (order by date1) +
        (value = 'B')::int
       )
from t
order by num, date1;

Here is a db<>fiddle.

score 0 · Answer 2 · answered Dec 21 '20 at 20:16

I think you want to increment the counter only when the value on the current row is the same as in the previous row. You can do this with lag() and a cumulative sum():

select id, value, date1,
    sum(case when lag_value <> value then 0 else 1 end) as rnk
from (
    select t.*,
        lag(value) over(order by date1) as lag_value
    from mytable t
)

row number in sql -keep it same for change in value

2 Answers2