8251

Create identifier based on some of the columns with shared data

Question:

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.

<a href="https://i.stack.imgur.com/POOhE.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/POOhE.png" data-original="https://i.stack.imgur.com/POOhE.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

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?

Answer1:

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

Recommend

  • Change character in Tedit Delphi
  • How to parse this format of xml data?
  • Query with multiple values in a column
  • Flipping and maintaining aspect ratio of a chart in ggplot2 [duplicate]
  • How select second line from top 2 or something similar?
  • Row_number() function for Informix
  • Is there a way to pivot a customer ID and a their most recent order dates?
  • Azure Service Bus topics partitioning
  • Oracle ListaGG, Top 3 most frequent values, given in one column, grouped by ID
  • Azure table query partial partitionkey guid match
  • write text on image and show it to a imageview
  • Certain Arabic text gets incorrectly shown while other Arabic text gets showed normally?
  • How to change placeholder text in an autocomplete activity of android google place?
  • What Makes These Two Array Adds Different?
  • C# fibonacci function returning errors
  • Merging rows to columns
  • Bigquery event streaming and table creation
  • Cannot upload to OneDrive using the new SDK
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • htaccess add www if not subdomain, if subdomain remove www
  • Ionic 2 storage is not cleaning up on uninstall - Only for signed APK
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • JSON response opens as a file, but I can't access it with JavaScript
  • Change Inet root folder for iis 7
  • Accessing IRQ description array within a module and displaying action names
  • Date difference with leap year
  • Is possible to count alias result on mysql
  • Updated Ionic CLI but shows previous version (Windows)
  • Convert array of 8 bytes to signed long in C++
  • Hazelcast - OperationTimeoutException
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Understanding cpu registers
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • apache spark aggregate function using min value
  • Can't mass-assign protected attributes when import data from csv file
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Unable to use reactive element in my shiny app
  • Conditional In-Line CSS for IE and Others?