Create identifier based on some of the columns wit

问题:

In the example below the column sequence should be a identifier for rows that share the same value in some of the columns - compania, hrEntr, hrSaida, durJornada, durInterv, iniInterv, termInterv, sistema_horario, turno - however it should not take into calculation one of the columns - dia.

So as described in the picture the first five rows share these columns so the sequence should be 1. Row 6, which doesn't share all of the previous values should have it's own sequence number set to 2.

I have worked with ROW_NUMBER() OVER (PARTITION BY... but it creates the opposite result i.e. it starts over when the matching columns stop.

Is there a way to create my desired result?

回答1:

you can use RANK() function. Check if this solve your need:

drop table if exists stackoverflowTbl;
/********************************************************** DDL+DML */
create table stackoverflowTbl(id int identity (1,1), txt int)
GO
insert stackoverflowTbl (txt) values (1),(1),(2),(1),(3),(22),(22)
GO
select * from stackoverflowTbl
GO
/********************************************************** solution */
select id,txt,
    ROW_Number () OVER (order by txt) - RANK ( ) OVER ( partition by txt order by id ) as MySequence 
from stackoverflowTbl
GO