SQL RANK() window function-CodePudding

I have a table that looks like this:

question_id    message_id    responder
          1             1    user_a
          1             2    user_b
          1             3    user_b
          1             4    user_a

I currently have RANK() OVER (PARTITION BY question_id, responder ORDER BY message_id) but that produces this:

question_id    message_id    responder    rank()
          1             1    user_a            1
          1             2    user_b            1
          1             3    user_b            2
          1             4    user_a            2

Desired output:

question_id    message_id    responder    rank()
          1             1    user_a            1
          1             2    user_b            2
          1             3    user_b            2
          1             4    user_a            3

CodePudding user response：

It seems you are wanting to tracking when the user id changes when ordered by message id. Which is what CONDITIONAL_CHANGE_EVENT does for you

CONDITIONAL_CHANGE_EVENT(responder) OVER (PARTITION BY question_id ORDER BY message_id) b

CodePudding user response：

with data as (
    select *,
        coalesce(lag(responder) over
            (partition by question_id order by message_id), '') as last_responder
    from T
)
select *,
    sum(case when responder <> last_responder then 1 end)
        over (partition by question_id order by message_id) as "rank"
from data;

I had accidentally left out the latter half of the sum() over (...).

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c23682c54dfb9f5eb39c3f7bb60347f0