Increment row number based on value 1 and value 2

Question

How can I create a sequential value based on two rows within a table, for example, let's say I have a table containing an employee's ID and work state. I would expect the following values:

ID     State    Expected Value
-----------------------------
1      NY       1
1      PA       2
1      NY       1
2      NC       1
2      FL       2
2      MN       3

Answer 1

You can use dense_rank():

select t.*,
       dense_rank() over (partition by id order by state) as expected
from t;