title: adding repeating sequence numbers to a column in SQLite database based on conditions
body: <p>I added a column in my SQLite database, and I need to insert repeating sequence numbers, starting with 1...n BUT it's based on grouping by other columns. The sequence needs to start over at 1 again when there is a new grouping. </p>
  
  <p>Here is my table:
  <code>CREATE TABLE "ProdRunResults" ("ID" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , "SeqNumbr" INTEGER, "Shift" INTEGER, "ShiftSeqNumbr" INTEGER, "Date" DATETIME, "ProdRunID" INTEGER, "Result" VARCHAR)</code></p>
  
  <p><a href="https://i.stack.imgur.com/JQfmC.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JQfmC.jpg" alt="enter image description here"></a></p>
  
  <p>ShiftSeqNumbr is the new column that I need to populate with sequence numbers, based on grouping of numbers in ProdRunID column then by numbers in the Shift column.</p>
  
  <p>There could be up to 3 "shifts" (work shifts in a 24 hr period).</p>
  
  <p>I scraped together some code to do this but it adds the sequence numbers to ShiftSeqNumbr column in reverse (descending) order:</p>
  
  <pre><code>UPDATE ProdRunResults 
  SET ShiftSeqNumbr = (SELECT COUNT (*)
  FROM ProdRunResults AS N
  WHERE N.ProdRunID = ProdRunResults.ProdRunID
      AND N.Shift = ProdRunResults.Shift
      AND N.ShiftSeqNumbr = ProdRunResults.ShiftSeqNumbr);
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/lKo2r.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lKo2r.jpg" alt="enter image description here"></a></p>
  
  <p>How can I change the Update statement so the sequence numbers start at 1 and go up? Or is there a better way to do this?</p>